# VLOOKUP based on multiple IF criteria

#### elmacay

##### Board Regular
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

Last edited:

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Andrew Poulsom

##### MrExcel MVP
Formula in J2 copied down:

Excel 2010
ABCDEFGHIJ
1ABCDEF
21151103SJT94i1103SJ10T94j
32121103SJT94j1103SJ15T94i
417291103SJT94k1103SJ17T94k
514801103SJT94k1103SJ10T94j
Sheet1
Cell Formulas
RangeFormula
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

##### Board Regular
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

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

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

#### elmacay

##### Board Regular
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

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

Replies
7
Views
396
Replies
3
Views
428
Replies
0
Views
287
Replies
9
Views
462
Replies
3
Views
394

1,191,275
Messages
5,985,707
Members
439,975
Latest member
eelyn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back