INDEX MATCH Multiple criteria
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
-
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")
-
It returns that syntax isn't right...
-
Can you paste your formula and I can review it?
-
It actually worked, thanks for the help!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives