VLOOKUP on a reference sheet gives me a "no match" headache

Philipp Zünd · 09/14/20

I've been trying to do a simple VLOOKUP, and I read lots of help and community articles. Nothing helps. Anything I'm doing wrong here? (BTW: if I am using "75100" instead of ID35, it works fine).

John Jonassen · 09/14/20

You're missing your TRUE or FALSE at the end

...3,"FALSE") - for exact match

...3,"TRUE") - for closest match.

David Tutwiler · 09/14/20

What kind of error are you getting back? #NOMATCH or error or the wrong number?

Also, are you copying the formula down and it gives you issues, or is it an issue on this one line?

Kelly Drake · 09/14/20

3 quick recommendations:

Change the names of your cell references so you can quickly ID what the formula is looking at.
I would also recommend that you replace VLOOKUP with an INDEX/MATCH since that's more optimized for smartsheet.
Use the @row cell reference instead of the numbers (i.e. ID@row instead of ID35)

Here's the syntax:

=INDEX([data point you're looking up], MATCH([unique ID on the sheet of this formula], [unique ID on the data point you're looking up sheet], 0))

Philipp Zünd · 09/15/20

https://community.smartsheet.com/discussion/comment/262726#Comment_262726

I'm getting a #NOMATCH, and the issue is on this line only (but of course it also happens when I copy it down to additional lines).

Philipp Zünd · 09/15/20

https://community.smartsheet.com/discussion/comment/262735#Comment_262735

Thank you Kelly -- this sounds promising, but how would you exactly write the syntax of [unique ID on the sheet of this formula], [unique ID on the data point you're looking up sheet]? I tried the following:

=INDEX({DB Sales Promotions Range 1}, MATCH([ID@row], [Promo ID], 0)) but no success. Specially the "unique ID on the data point you're looking up sheet" I don't know what exactly you mean.

Thanks for elaborating a bit further.

Philipp Zünd · 09/15/20

https://community.smartsheet.com/discussion/comment/262723#Comment_262723

Thank you. I've tried - no success. Also, I think the true and false should be written without quotation marks, no?

John Jonassen · 09/15/20

My bad. Without quotes. @Philipp Zünd

VLOOKUP on a reference sheet gives me a "no match" headache

Answers

Help Article Resources

Categories