# VLOOKUP based on two conditions

• My challenge is pulling in a data element from a file to an exisiting file based on two criteria.
Example:
give me the value in cell e1 if cell b1 matches AND

File 1
A B C
1 H12377 03/05/2013 123
2 H12377 03/27/2013 276
3 H32389 05/03/2013 335

File 2
H12377 03/27/2013 _________

how do I get C2 value from file 1 (276) into file2 since column A is not unique but column A plus Column B is unique?

• Re: VLOOKUP based on two conditions

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: VLOOKUP based on two conditions

There should be a better way, but I do something like this:

in file 1:
I would insert a column before "A" and use the formula A1=B1&C1, then copy paste to the end

in File 2:

column C formula = A1&A2
column D formula = vlookup(C1,[file1]A:D,4,0)

there might be a better answer but this makes the work

• Re: VLOOKUP based on two conditions

in file1
B6 = H12377
B7 = 27/03/2013 (the date to use in the right format)
Somewhere = =SUMPRODUCT((A1:A3=B6)*(B1:B3=B7)*(C1:C3))
Then cut and past the last formula in file2
It gives
=SUMPRODUCT(([VlookUp2conditionsFile1.xlsx]File1!A1:A3=[VlookUp2conditionsFile1.xlsx]File1!B6)*([VlookUp2conditionsFile1.xlsx]File1!B1:B3=[VlookUp2conditionsFile1.xlsx]File1!B7)*([VlookUp2conditionsFile1.xlsx]File1!C1:C3))

## Files

Triumph without peril brings no glory: Just try

• Re: VLOOKUP based on two conditions

Robert's formula must be confirmed using Control + Shift + Enter for arrays formulas

Triumph without peril brings no glory: Just try

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!