VLOOKUP resulting in some references coming back as #NO MATCH.
I have a master Smartsheet people fill-out a form for that submits purchase orders made. I have a corresponding Smartsheet that people fill-out a form for to indicate when a specific PO has been received. In the master Smartsheet I am attempting to use a VLOOKUP that looks at the PO number from the master Smartsheet and looks for the same one on the Shipments Received Smartsheet and then pulls over several columns worth of data.
As you can see in the screenshot, I am getting a lot of #NO MATCH errors, but I don't know why. I even copied the PO numbers from the Shipments Received Smartsheet and pasted them into the master Smartsheet to ensure they were indeed identical values, but I got the same errors on the same rows I previously did.
The formula, as seen in the screenshot, is =VLOOKUP([PO Number]@row, {Shipments Received Range 3}, 5) with column_num changing from 5 to 6, 7, 8, 9, & 10 since column 1 in the reference range is the same in all of them, 'PO Number'.
Why would some of the cells pull data but others have the #NO MATCH error despite the referenced PO number being identical in both sheets?
Answers
-
I hope you're well and safe!
Try adding false at the end of the formulas.
=VLOOKUP([PO Number]@row, {Shipments Received Range 3}, 5, false)
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Taylor Scott
Hope you are fine, i think the problem is with the format of the data because as i see in some rows the formula is working perfectly. I advise you to check the format and try to use Index with match formula.
=INDEX({Shipments Received Range 3}, MATCH([PO Number]@row, [PO Number]:[PO Number]), 5)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thanks, Andrée. Though I no longer get the #NO MATCH error, the values being returned are not accurate. The left side Smartsheet of this screenshot's data is being pulled by the right side Smartsheet.
Yellow (incorrect date and received by) and Green (incorrect date, and missing data about shipping issues) boxes are specific examples of this, but if you look at the whole screen shot, you'll see the other inaccuracies.
I forgot to screen-shoot the formula, but here it is:
=VLOOKUP([PO Number]@row, {Shipments Received Range 1}, 5, false)
Again, column_num changes from 5 to 6,7,8,9, and 10. Also, you'll notice that the range changed from 3 to 1, that's because I entirely re-did the referenced ranges to make sure there wasn't an error somewhere else.
-
Happy to help!
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks again, Bassam, for helping me out on this question, too!
Unfortunately, I got the same #NO MATCH error using your formula, too. I ensured the PO Numbers matched exactly by copying them from the shipment received Smartsheet and pasting them into the Smartsheet as seen in the screenshot.
-
I'll make sanitized copies of these and share them with you sometime today. I appreciate it!
-
I found the error.
You'll have to add the bolded part (0 at the end to indicate false, so it's an exact match) to each column formula, and then it should work.
=VLOOKUP([PO Number]@row, {COPYof Shipments Received Range}, 5, 0)
Did it work?
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Unfortunately this didn't work. Although it removed the #NO MATCH error, It isn't pulling the correct info over. For example, a couple of the 'Received By' names should be different names but aren't (all rows except 1 & 4 should be ES with 1 being JB and 4 KN), and row 15 should have a 'Yes' under 'Issues with shipping' then pull additional data over relating to the shipping issues. Also, the dates aren't the same, on multiple rows.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!