INDEX Match throwing incorrect answers

Kate H · 10/02/24

I am trying to use a unique identifier by column, to reference another table that has that unique identifier in it and provide it's adjacent value from another column, by row.

This is the formula I'm using and it's giving me values in that list but the wrong value:

=INDEX({Hours}, MATCH([Combined Work Description]@row, {Comb. Work Description}))

Where Hours is on another sheet and is listing the hours value I want returned. The Combined Work Description is the unique value, then the Comb. Work Description is the entire column of options for what the row's value for Combined Work Description could be. What is going on????

Courtney S. · 10/02/24

Try adding the "search type" parameter into your MATCH formula and setting it to zero to force it to only find exact matches. The default setting for the MATCH formula finds close matches / assumes values in the range are sorted in ascending order. So, try:

=INDEX({Hours}, MATCH([Combined Work Description]@row, {Comb. Work Description}, 0))

See the MATCH function page for details:

MATCH Function | Smartsheet Learning Center

Could also review the INDEX function to make sure it's how you want it: