INDEX MATCH Multiple criteria

Faisal F
Faisal F ✭✭
edited 06/09/22 in Smartsheet Basics

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

Answers