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.
