VLOOKUP selectively returning the information into sheet

Good day

I have been working on a couple of sheets where I gather the information with the help of a VLOOKUP.

I originally worked with about 10 stock items to get all the formula work done, and everything worked perfectly. I have added all the stock items which now adds up to 494 line items.

In the example below, you can see that some of the items bring in the correct VLOOKUP in the identifier column as well as in the Serialized? column, but others only give #NO MATCH result even though it is the exact same formula, pulling the information from the exact same sheet.

Am I missing something. I have re-done these formulas and looked at all the formulas to make sure the reference the correct sheet and have the correct columns. And they do.

SHEET A

The sheet below is the source sheet for the VLOOKUP, which has the original 494 stock items.

So if you select the dropdown in the sheet above, the identifier and Seriliazed? will populate automatically due to the vlookup.

SHEET B

Is there any other option that I can use to get the identifier and serialized? column to automatically populate as soon as the Stock item is selected in the description column (Sheet A), other than using VLOOKUP.


Also what would the reason be that the vlookup works perfectly for some, but not for others? Is me sheet too large? Too many cells?

Any assistance will be greatly appreciated.


Kindest regards,

Michelle

Michelle Basson

Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet

https://www.linkedin.com/in/michelle-basson/

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Make sure that it is an exact match on both sheets to include capitalization, punctuation, and spacing. You will want to go into each cell and verify the spacing. You can put 23 spaces between two words but Smartsheet will only display 1 space. But then when you go to compare the two cells they won't match.

    If the working sheet is using a dropdown and you have each item listed only once in the source sheet, you should be able to copy all of the cells in that column from the source sheet and paste them directly into the column properties of the dropdown. This will ensure an exact match.

    Then go back through your working sheet and update the rows with errors to the new listing and see if that fixes the error.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!