Help with VLOOKUP on different worksheets

Question

I need help using the VLOOKUP formula.&nbsp; 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 same column headers, also column A,B,C.&nbsp; On this worksheet, Column A will have the product code manually typed in i.e. 123456.&nbsp; The Vlookup formula will be used in Column B to lookup on the source worksheet the product description in Column B that corresponds with the code typed in Column A.&nbsp; I have created a range name for my source worksheet of MasterCostData.&nbsp; I would then use same formula syntax to populate the cost for the code typed in in to column C.&nbsp;My formula in Column B is =VLOOKUP(A2,MasterCostData,2,FALSE) and I keep getting a N/A error.&nbsp; HELP!!&nbsp;&nbsp;&nbsp;My formula&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;with 3 columns - Column 1 product code, Column 2 product description and Column 3 cost.&nbsp; I am trying to write the VLOOKUP formula so that on a different worksheet I can physically type the Product code in a cell and one cell to the right the product description will populate from the source worksheet.&nbsp; And then again write the formula so that I can populate the cost based on the product code entered in a cell to the right of the product description.&nbsp;For example, I have created a range name of "MasterCostData" for the data from my source worksheet.&nbsp; I have the same&nbsp; table headers as my source data (Column 1 product code, Column 2 product description and Column 3 cost).&nbsp; If I type in a product code of 123456 in cell A9 of the Product Code column&lt; I want Excel to Look for that value in the same column of my source worksheet (MasterCostData) and populate the product description for that code in the Product Description column.. So, in the Product description column I have typed =VLOOKUP(A9, MasterCostData,2,0).&nbsp; I continuously get a N/A Error message.&nbsp;HELP!!

amiller2325 · Answer

Sorry very confusing - I forgot to delete the last part of the post.&nbsp; Please only respond to first paragraph.

chrismendoza · Answer

amiller2325&nbsp;-&nbsp;Your values in [CODE] Named Range = 'MasterCostData' are stored as Text. The data entry in $A$2 is General however, the value 110161 is understood to be Number.&nbsp;Therefore, 110161 &lt;&gt; '110161'.&nbsp;&nbsp;

mathetes · Answer

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....

amiller2325 · Answer

mathetes&nbsp;How do I change the code in Master Data sheet from text to numeric?

mathetes · Answer

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...

Forum Discussion

Help with VLOOKUP on different worksheets

5 Replies