Ecxel function to test number range and return data in a cell

[MarleneVorster]

I need to test a postal code in one sheet to a range of codes in another sheet and return the data in a specified cell.

EG: Client's postal code is 0001, test against the Postal Code ranges in col A and return the relevant Province in col B.

<colgroup><col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:8777;width:180pt" width="240"> </colgroup><tbody>
[TD="class: xl67, width: 94"]Postal codes[/TD]
[TD="class: xl67, width: 240"]Province[/TD]

[TD="class: xl65, width: 94"]0001–0299[/TD]
[TD="class: xl65, width: 240"]Gauteng[/TD]

[TD="class: xl65, width: 94"]0300–0499[/TD]
[TD="class: xl66"]North West[/TD]

[TD="class: xl65, width: 94"]0500–0698[/TD]
[TD="class: xl66"]Limpopo[/TD]

[TD="class: xl65, width: 94"]0699–0999[/TD]
[TD="class: xl66"]Limpopo[/TD]

[TD="class: xl65, width: 94"]1000–1399[/TD]
[TD="class: xl66"]Mpumalanga[/TD]

[TD="class: xl65, width: 94"]1400–1699[/TD]
[TD="class: xl65, width: 240"]Gauteng
[/TD]

</tbody>

I have tried If And functions without success. Any suggestions please.

 

[JackDanIce]

Welcome to the board.

As an array formula (type the formula in, then press CTRL+SHIFT+ENTER), this works:

=INDEX($B$1:$B$6,MATCH(VALUE($D$1),VALUE(LEFT($A$1:$A$6,4)),1))

However, you'll need to adjust to match your sheet names and ranges (unable to guess what they are from the information in the post)

 

[JoeMo]

Welcome to the forum!

Below is a user-defined function (UDF) that you can try along with an example of its use and instructions on how to install it in your workbook. The lookup table you posted has been given a defined name of "LkUpTable" which is for the range A2:B7 in the example.

Excel Workbook
	A	B	C	D	E
1	Postal codes	Province		Client Code	Province
2	00010299	Gauteng		0001	Gauteng
3	03000499	North West		0319	North West
4	05000698	Limpopo		1698	Gauteng
5	06990999	Limpopo		1700	#VALUE !
6	10001399	Mpumalanga			#VALUE !
7	14001699	Gauteng		0501	Limpopo
8				0700	Limpopo
Sheet1

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function Province(S As Variant) As String
Dim c As Range
If S < 1 Or S > 1699 Then
    Province = CVErr(xlErrValue)
    Exit Function
End If
For Each c In Range("LkUpTable").Columns(1).Cells
    If S >= Val(Split(c, Chr(150))(0)) And S <= Val(Split(c, Chr(150))(1)) Then
        Province = c.Offset(0, 1)
        Exit Function
    End If
Next c
End Function

Note that empty cells or cells with a code outside the table values cause the UDF to return the #VALUE ! error.

 

[Rick Rothstein]

This formula should also work...

=IFERROR(LOOKUP(D2,{1,300,500,699,1000,1400,1700},{"Gauteng","North West","Limpopo","Limpopo","Mpumalanga","Gauteng",""}),"")

 

[jtakw]

Hi,

If there's a chance the Client Post Code will not be in the Table range, use F2 formula (Change "No Match" to "" or whatever you like); otherwise use E2 formula, either formula copied down:

Book1
	A	B	C	D	E	F
1	Postal codes	Province		Client Post Code	Province
2	00010299	Gauteng		0001	Gauteng	Gauteng
3	03000499	North West		0319	North West	North West
4	05000698	Limpopo		1698	Gauteng	Gauteng
5	06990999	Limpopo		0501	Limpopo	Limpopo
6	10001399	Mpumalanga		0700	Limpopo	Limpopo
7	14001699	Gauteng			#N/A	No Match
8				1700	Gauteng	No Match
Sheet192

Cell Formulas
Range	Formula
E2	=LOOKUP(D2+0,LEFT(A$2:A$7,4)+0,B$2:B$7)
F2	=IF(OR(D2+0=0,D2+0>RIGHT(A$7,4)+0),"No Match",LOOKUP(D2+0,LEFT(A$2:A$7,4)+0,B$2:B$7))