Vlookup between 2 zip codes

[Roha1]

I need help here. I have tried to find an answer but nothing.

My problem is that I got lot of Zip Codes that connect to a spesific sales Rep.


A4 3000 B4 3069 C4 Person 1
A5 3070 B5 3299 C5 Person 2
A6 3300 B6 3599 C6 Person 3

F4 is the place I put the Zip code I want to find a match for.

Like this it goes on.

I found on that should have worked "=LOOKUP(2,1/($A$4:$A$80>=F4)/($B$4:$B$80<=F4);$C$4:$C$80)"

The reply I get is only "#DIV/0!"
What is wrong with this?

 

[Scott T]

Is this what you want?

Excel 2010
	A	B	C	D	E	F	G
4	3000	3069	Person 1			3778	Person 7
5	3070	3299	Person 2
6	3300	3599	Person 3
7	3600	3669	Person 4
8	3670	3699	Person 5
9	3700	3776	Person 6
10	3777	3799	Person 7
11	3800	3899	Person 8
Sheet4

Cell Formulas
Range	Formula
G4	=LOOKUP(F4,A4:A80,C4:C80)

 

[Roha1]

That worked, some of the times, but I did several test, and a lot of the time it is not correct It finds names that do not correspond to the correct number :/

 

[Roha1]

Never mind, I got it to work, I just had to sort it correctly


Thanks a lot for the help

 

[sgroath]

if your data is not sorted or has gaps in ranges covered, you can use this instead:

=INDEX(C4:C80,MAX((A4:A80<=F4)*(B4:B80>=F4)*(ROW(A4:A80)-ROW(A4)+1)))

Key - instead of entering this formula and hitting enter, hit Ctrl+Shift+Enter instead to make this an array function. It will show up in the formula bar surrounded by braces ( {=INDEX.....+1)))} ). Do NOT type in the braces manually.