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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!