How to get rid of #notmatch in Vlookup
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?
Answers
-
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.
-
KDM,
that did not work! :(
-
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.
-
Were you able to to resolve your #NoMatch error?
-
no, I also tried putting FALSE or ,0 at the end of it and still get the same result.
-
Would you mind sharing your formula with the IFERROR, please
thanks,
Kelly
-
=IFERROR(VLOOKUP([Production Order]@row, {ShortageSummary (2) Range 1}, 3), "")
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!