VLOOKUP based on multiple IF criteria

[elmacay]

Hi all,

In a file with 40,000 combinations of zipcodes and house numbers, I want to vertically look up the corresponding neigborhood code. Problem is, I can't use a standard VLOOKUP. Depending on the house numbers (odd or even) and until which house number the zipcode runs, there are different neighborhood codes.

For example: A house in row 2 has zipcode 1103SJ (cell X2) has house number 10 (cell Y2). Which neighborhood code in column F corresponds with that? See table beneath.

In words, the formula would be something like this:
- if X2 (1103SJ) corresponds with column E and
- if Y2 is even, then
- if Y2 is greater than or equal to column C and Y2 is smaller than or equal to column D
- if Y2 is odd, then
- if Y2 is greater than or equal to column A and Y2 is smaller than or equal to column B

So 1103SJ number 15 would return T94i, 1103SJ number 10 would return T94j, 1103SJ number 17 would return T94k, as would 1103SJ number 14.

A . . B . . .C. . . . D. . .E. . . . . . . . . F
1 . . 15 . . . . . . . . . . 1103SJ . . . . T94i
. . . . . . . .2 . . . 12. . 1103SJ . . . . T94j
17 . .29 . . . . . . . . . .1103SJ . . . . T94k
. . . . . . . 14 . . . 80. .1103SJ . . . . T94k

Any help is greatly appreciated. Thanks in advance.

Cheers, Elmacay

 

[Andrew Poulsom]

Formula in J2 copied down:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E	F
2	1	15			1103SJ	T94i		1103SJ	10	T94j
3			2	12	1103SJ	T94j		1103SJ	15	T94i
4	17	29			1103SJ	T94k		1103SJ	17	T94k
5			14	80	1103SJ	T94k		1103SJ	10	T94j
Sheet1

Cell Formulas
Range	Formula
J2	=INDEX(F$2:F$5,MATCH(1,INDEX(((E$2:E$5=H2)*(IF(ISODD(I2),A$2:A$5,C$2:C$5)<=I2)*(IF(ISODD(I2),B$2:B$5,D$2:D$5)>=I2)),),FALSE))

 

[elmacay]

Hi Andrew, thanks a lot for the formula! It works like a charm, like last time you helped me. I encounter another problem, when adapting it to my exact needs.

The values in H2 and I2 are combined in my original table, to make a unique combination of zipcode and number. For example 1103SJ10. I take the left 6 to make H2/zipcode, and all positions on the right minus 6 for the house number.

Your formula, which works great: =INDEX($F$2:$F$5,MATCH(1,INDEX((($E$2:$E$5=H2)*(IF(ISEVEN(I2),$A$2:$A$5,$C$2:$C$5)<=I2)*(IF(ISODD(I2),$B$2:$B$5,$D$2:$D$5)>=I2)),),FALSE))

Adapted formula, which still works, with 1103SJ10 in H2: =INDEX($F$2:$F$5,MATCH(1,INDEX((($E$2:$E$5=LEFT(H2,6))*(IF(ISEVEN(RIGHT(H2,LEN(H2)-6)),$A$2:$A$5,$C$2:$C$5)<=RIGHT(H2,LEN(H2)-6))*(IF(ISODD(RIGHT(H2,LEN(H2)-6)),$B$2:$B$5,$D$2:$D$5)>=I2)),),FALSE)). Notice the bold faced I2 at the end of the formula. I want to replace that as well with RIGHT(H2,LEN(H2)-6), making the complexte formula:

=INDEX($F$2:$F$5,MATCH(1,INDEX((($E$2:$E$5=LEFT(H2,6))*(IF(ISEVEN(RIGHT(H2,LEN(H2)-6)),$A$2:$A$5,$C$2:$C$5)<=RIGHT(H2,LEN(H2)-6))*(IF(ISODD(RIGHT(H2,LEN(H2)-6)),$B$2:$B$5,$D$2:$D$5)>=RIGHT(H2,LEN(H2)-6))),),FALSE)) Strange thing is, this gives me a #N/A as a result. I think I should be able to substitute I2 with RIGHT(H2,LEN(H2)-6), right?

Thanks, Elmar.

 

[Andrew Poulsom]

The RIGHT function returns text, so you need to coerce it into a number:

RIGHT(H2,LEN(H2)-6)+0

 

[elmacay]

Aha, I understand. In the middle, there's the same, but it's multiplied by (IF(ISODD....), so it turns into a number as well and doesn't give me the #N/A
Many thanks for the help and the quick reply.

 

[Andrew Poulsom]

The ISODD function is doing the coercion. Some functions do that.