VLOOKUP resulting in some references coming back as #NO MATCH.

Taylor Scott

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?

Andrée Starå

Hi @Taylor Scott

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!

Bassam Khalil

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)

Taylor Scott

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.

Andrée Starå

@Taylor Scott

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, [email protected])

Taylor Scott

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.

Taylor Scott

https://community.smartsheet.com/discussion/comment/285295#Comment_285295

I'll make sanitized copies of these and share them with you sometime today. I appreciate it!

Andrée Starå

@Taylor Scott

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!

Taylor Scott

https://community.smartsheet.com/discussion/comment/286597#Comment_286597

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.