INDEX MATCH Multiple criteria

Faisal F

Hello,

I am trying to get the zip code based on city and state from a different sheet. The sheet that I want to return information from has 3 columns: City, State, Zip code. There are 20,000 rows in the sheet that I am trying to use as a reference with all the cities in the United States

The sheet that I want to write the formula in has city and state. I want to return the zip code for that specific city and state.

I tried using this formula and it is returning correct data from some of the city/states (not all):

=INDEX({Range for value returned}, MATCH(State@row, {Range for State}) + MATCH(City@row, {Range for city}))

**Note: some city names are duplicated as well as the state name

**Note: I can't use VLOOKUP function because columns are not in the same order

Sameer Karkhanis

Try this: (Make sure you replace the range names based on what you call it)

=IFERROR(INDEX(COLLECT({Zip Code Range Ref Sheet}, {State Range Ref Sheet}, HAS(@cell,State@row), {City Range Ref Sheet}, HAS(@cell, City@row)),1),"Not Found")

Faisal F

https://community.smartsheet.com/discussion/comment/332518#Comment_332518

It returns that syntax isn't right...

Sameer Karkhanis

Can you paste your formula and I can review it?

Faisal F

It actually worked, thanks for the help!