Index Match return "NO MATCH" when it should match

William.T_Genisium

Hi, I started using Smartsheet recently to convert an old Excel spreadsheet. I have gotten better with help of multiple community posts, but I juste encountered a problem thet does not make sense in my opinion.

I am working with references.

In the "Actual Gate" column, I successfully return the value from a referenced table depending on the latest date inserted in a range of date cells.

Then, in the Next Gate column, I want to return the Next Gate according to the value in the actual gate column.

This should be a simple Index/Match functions, but it returns "NO MATCH"

I tried to separate the formula but even when I use one cell to index the 5th value from the reference and another cell to match the value indexed above in the same referenced, it still returns "NO MATCH"... I have joined some screenshots

Paul Newcome

Try indicating an exact match in the MATCH function.

=INDEX({Range}, MATCH([Column Name]@row, {Range}, 0))

William.T_Genisium

I did not want the entry to be too long, but the weirdest thing is that it works for some entries but does not work for others, you can see that the index match works for the 2nd value but the match does not work for the 6th. Here is the referenced range.

Paul Newcome

Try indicating an exact match in the MATCH function.

=INDEX({Range}, MATCH([Column Name]@row, {Range}, 0))

William.T_Genisium

It worked!! Thank you very much!

Paul Newcome

Happy to help. 👍️