How to use MATCH function on multiple columns at the same time?
I've used the MATCH function before on the "City" and "CityMATCH" column. It worked out well. This is the formula that I used: =MATCH(City1, CityMATCH:CityMATCH, 0).
I've now added another column called "Number" and "NumberMATCH." What I want to know is whether "3" in the "Number" column and "Chicago" in the "City" column matches anything in the "NumberMATCH" and "CityMATCH" columns.
The result should tell me that "2 Chicago" has no match, because there is no "2 Chicago" in the "NumberMATCH" and "CityMATCH" columns. However, it should tell me that "3 Chicago" does have a match and it is in row 2. I tried combining the AND and MATCH functions together but it didn't give me the result that I want. Can anyone please help me?
Best Answer
-
The easiest way to do this would be to insert a text/number column and then use this...
=NumberMATCH@row + "" + CityMATCH@row
Then you can use something along these lines to get the row number of the match...
=MATCH(Number@row + "" + City@row, [Number/City]:[Number/City], 0)
Answers
-
The easiest way to do this would be to insert a text/number column and then use this...
=NumberMATCH@row + "" + CityMATCH@row
Then you can use something along these lines to get the row number of the match...
=MATCH(Number@row + "" + City@row, [Number/City]:[Number/City], 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!