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

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: