INDEX MATCH MATCH ISSUE

ckcalvins

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

Paul Newcome

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.

Paul Newcome

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.

ckcalvins

Beautiful, worked like a charm. Why wouldn't they just allow you to make a reference to Column headers? Seems ridiculous.

Paul Newcome

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.