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?

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.

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