Forum Discussion
Help with VLOOKUP on different worksheets
I need help using the VLOOKUP formula. I have a source data worksheet Columns A,B,C are Product Code, Product Description, Product Cost respectively. In the next worksheet I have a table with the sa...
Sorry very confusing - I forgot to delete the last part of the post. Please only respond to first paragraph.
amiller2325I see the problem just looking at your sheets. The code in your master data sheet is in text form; in your other page its numeric. That's an easy mistake to make, but I'm quite sure that's the issue.
FWIW, the named range for the master data was not as simple as it could have been....
mathetes How do I change the code in Master Data sheet from text to numeric?
- It might be problematic even if you did, given numbers like the first one 00287. the leading zeroes disappear when entered as a number.
So entering them in Sheet1 as text is another solution. Just precede the digits with a single ' sign. That renders what follows as text,.
I am noticing another issue though. Some of your code entries have trailing spaces--those are invisible to the eye, but very visible to Excel. Which is to say, "1049664 " is not the same as "1049664"
Are these Codes cast in stone? Do they come from elsewhere, a provider or vendor, so that you have no choice in them? I ask because another (obvious) solution would be to create codes that ARE a combination of letters and numbers, so there's not the ambiguity. Any ambiguity like this will almost inevitably lead to #NA errors along the line. But also get rid of trailing (or leading) spaces...
