Hi, I need assistance on how to create a MS Excel formula which matches criteria from two different MS Excel files. i.e. I have columns named Part No, Plant No, Batch No, Value and Qty on both sheets, one file is from Jan and the other from Feb. I would like to have the formula state, “if Part No, Plant No and Batch No match between both MS spreadsheets, place the value (of the Jan spreadsheet) into this cell. How would I nestle my statements? An example would help

I'm not sure what you mean by " ...place the value (of the Jan spreadsheet) into this cell."What value from the Jan spreadsheet?

In any case, maybe this will help...

I'll let you worry about file names and sheet names, and just offer the syntax for a possible solution.

Let's say I have this data:

A B C D 1 Part # 1 Plant # 1 Batch # 1 myValue 2 Part # 1 Plant # 1 Batch # 1I only want to return "myValue" if the 3 values in Row 1 match all three values in Row 2. I think that's similar to what you want. Correct me if I'm wrong.

I would use this formula:

=IF(VLOOKUP(A1,A:A,1,0)&

VLOOKUP(B1,B:B,1,0)&

VLOOKUP(C1,C:C,1,0) =

VLOOKUP(A2,A:A,1,0)&

VLOOKUP(B2,B:B,1,0)&

VLOOKUP(C2,C:C,1,0),

VLOOKUP(A1,$A$1:$D$1,4,0),"")I split the formula into multiple lines to make it easier to read.

What it does is concatenate the results of the first three VLOOKUPs and compare that string to the concatenated results of the second three VLOOKUPs. If the string matches, then the last VLOOKUP returns the value I want. If not, I get nothing.

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History