Vlookup between 2 zip codes

Roha1

New Member
Joined
Jul 20, 2017
Messages
15
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

Well-known Member
Joined
Dec 14, 2016
Messages
2,587
Office Version
365, 2016
Platform
Windows
Is this what you want?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3069</td><td style=";">Person 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3778</td><td style=";">Person 7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">3070</td><td style="text-align: right;;">3299</td><td style=";">Person 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">3300</td><td style="text-align: right;;">3599</td><td style=";">Person 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">3600</td><td style="text-align: right;;">3669</td><td style=";">Person 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">3670</td><td style="text-align: right;;">3699</td><td style=";">Person 5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">3700</td><td style="text-align: right;;">3776</td><td style=";">Person 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">3777</td><td style="text-align: right;;">3799</td><td style=";">Person 7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">3800</td><td style="text-align: right;;">3899</td><td style=";">Person 8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G4</th><td style="text-align:left">=LOOKUP(<font color="Blue">F4,A4:A80,C4:C80</font>)</td></tr></tbody></table></td></tr></table><br />
 

Roha1

New Member
Joined
Jul 20, 2017
Messages
15
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

New Member
Joined
Jul 20, 2017
Messages
15
Never mind, I got it to work, I just had to sort it correctly :)


Thanks a lot for the help :)
 

sgroath

New Member
Joined
Apr 8, 2014
Messages
31
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.
 

Forum statistics

Threads
1,081,748
Messages
5,361,057
Members
400,611
Latest member
ThebigG

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top