NO MATCH error when a match exists

g_crosley19

Hello - I'm stumped - I'm using a VLOOKUP to find values that I know for an absolute fact are in the first column of the reference table, but it still returns No Match. Is there something wrong with my formula? This is a screenshot of my formula in the sheet I'm trying to bring the info back to. The AAP ID-2 column is a unique number. In the "Reference" sheet, I have the same Unique number in the FIRST column of the Reference table, and I'm asking it to bring back the data in column 3.

Thoughts?

Courtney S.

Double check that both columns are set up exactly the same way, and that the values in both columns are both considered numbers, or both considered text. Also double check that the selected range in that cross-sheet reference starts with the ID column as the leftmost column. You could also try rewriting the formula to be an INDEX/MATCH formula instead of VLOOKUP.

Formula combinations for cross sheet references | Smartsheet Learning Center

g_crosley19

Hello, I have ensured all of that is done. The text in both columns is set to Text, the numbers are exactly the same (when I do a Control Find, I can locate each one individually) and the Reference Range has the lookup in the left-most column. I'm struggling with INDEX/MATCH as well - I see people on these forums keep suggesting that but I've seen there's been a bug with VLOOKUP - is it possible that's still occurring?

Melissa Yamada

Hello @g_crosley19

You can try this INDEX/MATCH formula:

=INDEX({Column 3}, MATCH([AAP ID-2]@row, {Unique # Column in Ref Sheet}, 0))

You can also contact Smartsheet Support to check why your formula returns a no match given that your formula is correct and the unique ID is found on both of your sheet.

g_crosley19

I tried the INDEX Match formula and it's doing the same thing - displaying a "NO MATCH." I will try support. This seems to be a bug.

g_crosley19

Well, just for an update - the sheet is actually flashing between returning the data and "No Match." Like I'm literally watching it switch back and forth. There's no rhythm or reason to it. Something is buggy.

Melissa Yamada

@g_crosley19 given that situation then it might be the best option to contact Support for further investigation.