• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using vlookup() to find a value using vlookup() on a 2nd table

M

moodhairboy

New Member
Hi,


I am not sure if this is the right solution the vlookup() calling a second one to get a value. The main reason I am not sure is simply because it isn't working. Here's is what I am trying to do.


In worksheet A - I Have a value 12345 in a call A1 that is the result of a vlookup in worksheet b.


In worksheet C - I have a two colum list that is composed of a numeric employee id and the second column is the name.


Worksheet B is my raw data that is compiled from data extracts from several systems. Worksheet A is a summarized list of the key data records I need.


Worksheet C and others are reference tables that allow me to convert short address codes into full names, employee numbers into employee names etc.)


The formula that I am trying to use is this:


=VLOOKUP(AF3,Buyers!$A$2:$B$120,2,FALSE)


I lookup the value in 'AF3' (which itself is a vlookup result of worksheet B) and then attempt to look up that value in "Buyers" 'worksheet b' and get the result in col 2 if it matches AF3.


I get #N/A all the time.


Any ideas?
 
mind posting the workbook online?


my guess is that your somewhere in the second datatable something is wrong. an extra space between number/text. Have you checked using crtl-F to find the intended result of AF3 on the 2nd table?
 
Hi ,


Check out the following :


When you say Worksheet , is it a tab or a Workbook ?


When you use the formula given as


=VLOOKUP(AF3,Buyers!$A$2:$B$120,2,FALSE)


Excel expects that Buyers is a worksheet tab that has been renamed from its default name of Sheet1 or Sheet2 or ... to Buyers. If it does not find any tab named Buyers , then it expects that Buyers.xlsx will be found elsewhere as a separate file in your system. If this file is not open when you insert this formula , then it will prompt you to open this workbook. In the second case , once the formula is correct , the text Buyers ( in the formula ) will be replaced by the text '[Buyers.xlsx]Sheet1' in the formula.


Secondly , when you get any error in a worksheet due to a formula not working the way you expect it to , select that cell , and click on the Evaluate Formula button under Formulas -> Formula Auditing. As you step through the formula , you can identify at what stage the formula is giving the error.


The cell AF3 may not contain what you think it contains ; if you want to verify this , find out manually which cell in your lookup table contains the same value ; let us assume this value is in cell A73. In any unused cell , put in the formula =AF3=Buyers!A73 , and see whether you get TRUE or FALSE.


Narayan
 
I just read re may post and probbaly made it sound different than the reality of the situation.


I have one workbook - with three spreadsheets and eash sheet has been renamed. One is called 'Tables


Another tab is called 'PR' and it contains a list of data exported from another system and imported into excel into this sheet. The sheet is 8 columns of numbers and text. The buyer number (######) is one of the fields populated for each row. It is the numeric ID of the individual that processed the Purchase Request.


In the 'Buyer' sheet the first column is a list of the numeric id's of each buyer, the second column is the alphabetic name. For example, 123456, Joe Smith


In the Master Sheet I have a field that contains the buyers "######" derived by using a vlookup formula to read the tab "PR" and use a value from the master list in another cell that is the "PR Number". The formula finds the "PR Number" value probably, reads the "PR" tab and returns the "Buyers "######" properly.


I want to convert that buyer number into the alpha name (e.g. 123456 becomes Joe Smith).


So I use a vlookup formula to read the value in the field in the cell that comes from a vlookup forumla (the number) and try to use that value to lookup and return the name from the sheet named "Buyers".


I have tried the forumla editing which I use often and always get "N/A". It looks like it is not getting the value from the sheet "Buyers". Remember all three of these sheets are the same wortbook.


I'm wondering if I need a different formula. My logic is examples.


Take the value of A1 in 'Master List" and

find it in 'PR', if found return value in column 8 (which is the buyer #)

Take returned value of "Buyer number" and look in

look in sheet "Buyers" and find it in col 1 and return the value in Col 2 (Buyers name).


That is what I am trying to do.


Does this clear it up?
 
Hi ,


I am still not sure where the problem lies ; let me understand.


1. Suppose you have to retrieve the Buyer Name based on the numberic ID of the buyer , the formula would be


=VLOOKUP(123456,Buyer!Table_of_Buyers,2,false)


where 123456 would be the lookup value , and the range Table_of_Buyers would have the records of buyer numeric IDs and their names. Looking up this value of 123456 in the Table_of_Buyers , in the sheet Buyer , would return the name Joe Smith.


Now , you say that the value 123456 , in turn , comes through another lookup as follows :


=VLOOKUP('Master List'!G73,PR!B3:H307,8,false)


where the cell G73 in the sheet 'Master List' contains the value ######. Looking up this value in the sheet PR , in the table B3:H307 , would return the value 123456.


Is this correct ?


Narayan
 
Yes, But remember that the first vlookup gets the buyer number and the second vlookup gets the name associated with that buyer number.
 
fred,


I cannot post the actual workbook - proprietary and all that stuff. If the last post didn't help then I will create a workbook that has the three worksheets, data and formulas.
 
Hi ,


So , if the first lookup retrieves the buyer number , is it doing this correctly ?


If not , have you tried evaluating the formula , to see at what stage the formula returns an error value ?


Narayan
 
Yes - the first vlookup works correctly. It's the second v(lookup) that does not.


=VLOOKUP(AF3,Buyers!$A$2:$B$120,2,FALSE)


The value in AF3 is the result of a vlookup formula below:


=VLOOKUP(L3,PRALL,8,FALSE)


Well L3 hand inputed PR# and returns the buyer # in AF3. This lookup works fine.
 
Hi ,


In that case can you check up whether the data in column A in your Buyers ( or is it Buyer ) is Text while the data in AF3 is numeric or the other way round ?


Any mismatch in the data types will result in a #N/A value.


Narayan
 
You must log in or register to reply here.
Back
Top