VLOOKUP Issues when referencing another sheet?

Brandon R

I want to preface what may seem like rookie mistakes after rookie mistakes that I am still fairly new to Smartsheet and medium experience in Excel.

As the question states, I get a #NO MATCH error when referencing another sheet BUT only when the number in a respective cell is above 100. I imported an Excel file to use as a reference for future sheets to decrease reliance on manual data entry. But before the importation, I did not format my number column as text. Instead, I went the long route and joined a few columns to prevent Excel from stripping leading zeros. So I had '00 in front of single-digit numbers and '0 in front of double-digit numbers. Fast forward to the original vlookup I ran. Using =VLOOKUP([Store #]@row, {Open Store List 1-4-2024 Range 2}, 4, false) I was able to pull back data from my reference sheet so long as it was a number less than 100. Anything over that and I received the #NO MATCH error. It took some troubleshooting to realize what was going on. Unfortunately the only solution I could think of at the time was to manually add the ' in front of every digit over 100. Once I knocked that out, my vlookup worked for everything. Here's an example:

Now I'm running into the same issue on a different sheet. Any number less than 100 and I get results. Anything over and I get the #NO MATCH error. I'm using the same reference sheet that I was pulling data from in my first issue. All numbers over 100 have the ' in front of them still on the reference sheet. The new sheet I'm using the vlookup on is referencing a Store # column formatted as a drop-down. If I select anything above 100, error. If I manually add an apostrophe in front of the store #, it matches. Here's an example of the temporary fix:

I'm not sure what to do at this point. Should I scrap the reference sheet and import a new one correcting the format blunder on my part or is there a way I can fix this without going that route seeing as how I will have to re-link all of the vlookups I have already run?

Help is very much appreciated.

KPH

You can change the text string to a number using =VALUE([Store #]@row)

Which means

1. You could add another column to hold the numeric version of the store numbers and use that instead.
2. Or you could do the conversion within your VLOOKUP (instead of referencing [Store #]@row you use VALUE([Store #]@row)
3. Or you could add that other column (as in 1) then copy it and paste special/values over the top of what you had and delete the new column and thus wash your hands of the whole thing!

Brandon R

When using that formula you've provided it's stripping the leading zeros from my store numbers. I need to retain those since the company uses a three-digit store number format:

And were you saying to add VALUE into my formula string after VLOOKUP, =VLOOKUP(VALUE([Store #]@row, {Open Store List 1-4-2024 Range 1}, 3, false))

When plugging that in I get #INCORRECT ARGUMENT:

KPH

Yes, converting from text to number will remove the leading zeroes. That was the plan - to ensure your data was in a consistent format. You can retain your columns as they are, and do the conversion to number within the formula.

See the pink closing parenthesis? That needs to close the VALUE function. It should go after the @row in the formula.