How to get rid of #notmatch in Vlookup

Diego Casillas Zaragoza

hello,

I am using =VLOOKUP([Production Order]@row, {Test 1 Range 1}, 20) and it works but when there is no match it has a #nomach message on the cell. I would like to remove that. How can I do that?

Kelly Moore

Hey @Diego Casillas Zaragoza

Try

=IFERROR(VLOOKUP([Production Order]@row, {Test 1 Range 1}, 20),"")

This should return a blank cell instead of an error message. If you prefer something in place of a blank cell, insert the text in between the two " signs.

Diego Casillas Zaragoza

KDM,

that did not work! :(

Kelly Moore

You copy-pasted the formula into your sheet and the cell still produced a #NOMATCH error? Interesting. The formula produces a blank cell on my test sheet. Please double check that the quote signs you entered on the end are correct.

Kelly Moore

@Diego Casillas Zaragoza

Were you able to to resolve your #NoMatch error?

Diego Casillas Zaragoza

no, I also tried putting FALSE or ,0 at the end of it and still get the same result.

Kelly Moore

Would you mind sharing your formula with the IFERROR, please

thanks,

Kelly

Diego Casillas Zaragoza

=IFERROR(VLOOKUP([Production Order]@row, {ShortageSummary (2) Range 1}, 3), "")

Kelly Moore

Have you tried specifying the match-type as false? This says it's an exact match.

=IFERROR(VLOOKUP([Production Order]@row, {ShortageSummary (2) Range 1}, 3, false), "")

Just as a test, would you try an INDEX/MATCH? Although it should be no different in outcome, perhaps we'll see the desired result since we'll only pull specific columns instead of a table range. An INDEX/MATCH is not limited to a vertical lookup not a predefined table structure.

=IFERROR(INDEX({source sheet what you're trying to find}, MATCH([Target Sheet match]@row, {Source sheet match}, 0)),"")

The zero is part of the MATCH function and says the data is unsorted.

Kelly