Vlookup with two searches

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

I need to to create a vlookup that looks up within a table - first a zip code
(column a) and then a city within that zip code (column b) and returns me
columns b, c, & d.
 
Do you want data from B C and D all in one cell (separated by
commas?), or in 3 different cells?

Which cell is the zip code that you are looking up located?

Pete
 
I want b c and d in different cells.

The zip code to be looked up is on a separate sheet in column E. The city
to be looked up is in column D.
 
I need to to create a vlookup that looks up within a table - first a zip code
(column a) and then a city within that zip code (column b) and returns me
columns b, c, & d.
Click to expand...


Assuming your zip codes table is in range A2:A100, your city names
table is in range B2:B100, your zip code to look for is in cell E1,
your city name to look for is in cell F1, and that your output from
this lookup is to be placed in the three cells E2, F2 and G2 you can
try the following formula in cell E2 and copy it to cells E2:G2.

=INDEX(B$2:B$100,MATCH(1,($A$2:$A$100=$E1)*($B$2:$B$100=$F1),0))

Note that this is an array formula that has to be entered with
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Åke
 
if you can, use a database: they're much faster and more reliable for
these types of tasks.
 
Back
Top