Lookup with #NO MATCH error
I've used the Lookup formula to find hours per work order number. Until today, the formula reported "0" in the event the lookup value was not found. As of either Jan. 17 or 18 (today), it now reports "#NO MATCH" when the lookup value is not found.
Did something change recently with the way this formula works?
Answers
-
Having the identical problem. It just started.
-
Yes, as of today, the LOOKUP function now returns #NO MATCH if no match is found, whereas previously it returned a blank cell. We made this change to allow customers to use the IFERROR and ISERROR functions with LOOKUP, allow customers to distinguish between the case where no match is found vs. the case where a match is found & the return value is blank, and align with standard spreadsheet behavior.
If you'd like to customize what appears in the cell when a match isn't found, you can wrap the LOOKUP function with an IFERROR function.
For example, to show "N/A" in a cell in the case of no match, you could write:
=IFERROR(LOOKUP("Task E", [Task Name]1:Done5, 2, false), "N/A")Here's more info about IFERROR.
Best regards,
Daniel
-
could this also affect links between cells.? they are not working
-
I think that you might be experiencing a different issue.
Please reach out to our Support team to troubleshoot your cell linking issue further.
-
I spent several hours this morning fixing thousands of formulas on several complex Smartsheets because the functionality of the LOOKUP function changed in an incompatible way (#NO MATCH instead of a blank cell if no match was found). No notifications were sent out and the release note I found didn't mention the change in LOOKUP functionality. There are ways to do things like this without breaking existing formulas.
-
Jim,
Thank you for sharing, and many apologies for the inconvenience. We sent an email notice to LOOKUP users about this formula update, but it sounds like you may not have received it for some reason, and we are looking into this.
In this case we made the formula change for the sake of consistency and an overall better experience for customers. We realize some customers may wish to make formula updates as a result. Thanks again for your feedback and partnership as we make improvements to Smartsheet.
Daniel
-
How does one fix the following:
=IF(AND(Status139 = "Complete", Status140 = "Complete", Status141 = "Complete", Status142 = "Complete"), "Complete", IF((LOOKUP("Complete", Status139:Status142, 1, false) = "Complete"), "In Progress", IF((LOOKUP("In Progress", Status139:Status142, 1, false) = "In Progress"), "In Progress", "Not Started")))
-
Hi Mark,
It looks like you are using your LOOKUP functions to determine if a value exists at all in the range, so it should work to replace
LOOKUP("Complete", Status139:Status142, 1, false) = "Complete"
with
COUNTIF(Status139:Status142, "Complete") > 0
and also replace
LOOKUP("In Progress", Status139:Status142, 1, false) = "In Progress"
with
COUNTIF(Status139:Status142, "In Progress") > 0
Best regards,
Daniel
-
If there are those of you that really want to stick with LOOKUP and want that 0 back, SS treats #NO MATCH as and error. Consequently, you may use IFERROR to change the behavior/return value of the LOOKUP.
Returns a numerical ZERO >> =IFERROR(LOOKUP(([Column3]9, $[Column4]$14:$[Column5]$26, 2, false), 0)
Returns a string ZERO >> =IFERROR(LOOKUP(([Column3]9, $[Column4]$14:$[Column5]$26, 2, false), "0")
Hope that helps.
Best,
Joe
-
One quick follow up. I use this function to lookup a ranking score for my partners. If they're new and do not yet have a ranking, I return an empty string by simply using double quotes.
Returns an empty string >> =IFERROR(LOOKUP(([Column3]9, $[Column4]$14:$[Column5]$26, 2, false), "")
-
This change is crazy for database developers and takes away from SmartSheet. You need one's and zero's guys. Not arbitrary text. The IS ERROR functions are intended to help to eliminate syntax errors that are inadvertent, not those that are result related "errors". It's really not an error to not find a match. Sometimes, you don't want to find a match.
I'm trying to resolve this. I still get #NOMATCH
=IF([End. Inv.]@row = "", "", IF(VLOOKUP(Key@row, {Day 2 - Part 2 Range 1}, 16, false) = 1, 1, 0)) = #NOMATCH
=IF([End. Inv.]@row = "", "", IF(ISERROR(VLOOKUP(Key@row, {Day 2 - Part 2 Range 1}, 16, false) = 1, 1, 0)) = #NOMATCH
=IF([End. Inv.]@row = "", "", IF(VLOOKUP(Key@row, {BottleRock 2021 Day 2 - Part 2 Range 1}, 16, false) = "#NOMATCH", 1, 0)) = #NOMATCH
=VLOOKUP(Key@row, {Day 2 - Part 2 Range 1}, 17, false) = #NOMATCH
I'm not even trying to match anything. I'm actually getting #NOMATCH on things that match.
If you guys would consider going back to the industry standard that would be amazing!
-
Instead of using a VLOOKUP where the formula has to review all of the columns in your {range}, what about using an INDEX(MATCH? This way you only need to identify the two columns needed in order to bring back the value you're looking for.
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))
So in your case:
=INDEX({Column with value to return}, MATCH(Key@row, {Column with Key}, 0))
If this formula is still returning #NOMATCH, is it possible that the Key value on each sheet is being stored differently? Could you post a screen capture of each sheet, but block out sensitive data?
Cheeers,
Genevieve
-
Hi. Hoping I can gain some support on the below issue I am havign with VLOOKUP. I am attempting to do a basic VLOOKUP from one sheet to another where I lookup the value in the STATUS column of one sheet to the value in RPO STATUS column in another sheet and return back the value from POPP STATUS of the second sheet. But I keep getting #NO MATCH.
The formula I am using is =VLOOKUP(Status@row, {StatusGrid}, 2)
{StatusGrid} is the reference name to the second sheet containing columns RPO Status and POPP Status.
Thank you all in advance for your assistance.
-
Hey @midniterodeo
Try adding false to the end of the formula in case that helps:
=VLOOKUP(Status@row, {StatusGrid}, 2, false)
If you're still getting NO MATCH, is it possible that the values are somehow not exactly the same? Here are some tests you can run:
- Try using a simple COUNTIF to see if the values are being recognized across sheets:
=COUNTIF({RPO Status Column}, Status@row)
If this = 0, the values aren't matching.
- Do you have any formulas in your reference sheet? If there's an error in your {StatusGrid} referenced columns, this error will bubble up
- Try using an INDEX(MATCH with individual column references instead of a VLOOKUP:
=INDEX({Popp Status Column}, MATCH(Status@row, {RPO Status Column}, 0))
Let us know if you got it working or not!
Cheers,
Genevieve
-
You are the best! The addition of false at the end solved the issue. I'm glad this was a simple solution.
Thank you!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
