INDEX MATCH MATCH ISSUE
I cannot, for the life of me, figure out how to get the INDEX/MATCH/MATCH to work appropriately in Smartsheet. I use in Excel all of the time, but the 2nd MATCH calling a column I cannot understand how to create a reference to highlight all column headers only to make this work.
My formula:
=INDEX({Global RM Directory_Full Range}, MATCH([Assigned To]@row, {Global RM Directory_Colleague Name}, 0), MATCH("Colleague Department", {Global RM Directory_Full Range}, 0))
In essence, I'm searching the source table in full (Global RM Directory_Full Range), matching the "Assigned To" column of the current sheet to the "Colleague Name" of the source table row, but I know my issue is with the 2nd MATCH function. Since I cannot create a reference range that just includes the column headers like I would in Excel, I'm searching for "Colleague Department" but what can I put in place of the {Global RM Directory_Full Range} here?
Help please!! Returns #NO MATCH
Best Answer
-
You would need a "helper row" that houses the column headers in text version in the sheet. Then you would reference this row in the second MATCH.
Answers
-
You would need a "helper row" that houses the column headers in text version in the sheet. Then you would reference this row in the second MATCH.
-
Beautiful, worked like a charm. Why wouldn't they just allow you to make a reference to Column headers? Seems ridiculous.
-
Really it is pretty much the same as Excel except we are able to change "A" to "Column Name". In Excel, you don't change the "A". You use a row to manually enter the column names for a header row within the spreadsheet itself.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!