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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.7K Get Help
- 371 Global Discussions
- 205 Industry Talk
- 436 Announcements
- 4.5K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 129 Community Job Board
- 448 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!