VLOOKUP based on multiple IF criteria

elmacay

Board Regular
Joined
May 4, 2006
Messages
88
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top