#1
|
|||
|
|||
Two layer of vlookup issue
I have one excel file below:
document number line item Z 1000020 01 1000020 05 it will vlookup another excel file and get value column below: document number line item value 1000020 02 300 1000020 01 400 if I vlookup document number, my Z column for first record will be 300, which is wrong, I want vlookup by document number and line item together, how to do? |
#2
|
||||
|
||||
The only way I know to do that, jolinchew, is to put the two values into one cell. Let's call the two workbooks L (for "lookup") and T (for "target"). In T you have the document and line values in a helping column to the right, say cols I and J, an in col B you have the lookup value, the value to be found. In col A you put =I2&"."&J2; this creates in each row the document and line numbers with a period between them, eg "1000020.05".
In L you have the same document and line numbers somewhere off at the right, but your search function says =VLOOKUP(X2&"."&Y2,[etc]), looking in column A of the target worksheet. If that doesn't make sense to you, we need to be more explicit about what your sheets look like, perhaps post a sample. Last edited by BobBridges; 08-06-2013 at 11:53 AM. Reason: Posted too soon! |
#3
|
|||
|
|||
Hi jolinchew,
I agree with BobBridges. Otherwise you can use Microsoft Access. Unfortunately even with the PowerPivot you can't do it...maybe in the next versions... |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using Vlookup & IF together | thelauncher | Excel | 5 | 08-25-2013 11:32 PM |
IF and VLOOKUP | SBMC | Excel | 1 | 11-07-2012 10:24 AM |
Vlookup | kkerr | Excel | 6 | 08-31-2012 01:23 AM |
Vlookup | ibrahimaa | Excel | 5 | 03-04-2012 11:24 AM |
Help with VLOOKUP | sakhtar | Excel | 2 | 07-24-2010 07:39 PM |