Lookup Formula with two columns of data

[Johnny Thunder]

Hello friends,



I am in need of guidance for a formula.

I have 4 columns of data,

Column A is a name, Column b is an amount. I need to match this criteria up to column C and D which is the same as A&B just not sorted and with many more names to search through.

Normally I would have used a Vlookup but I am not sure how this would work since I need to match two criteria? any help will be appreciated. Thanks

Excel 2007

 

[taurean]

One way would be to concatenate items and then do VLOOKUP like:
Column E:
=CONCATENATE(C1,D1)
Column F:
=CONCATENATE(A1,B1)
Column G:
Normal VLOOKUP i.e. searching F1 in E Column.

 

[Jonmo1]

You can use an array something like this

=INDEX(E1:E100,MATCH(A1&B1,C1:C100&D1:D100,0))

This returns a value from column E, where Column C&D match A1&B1

IMPORTANT
This is an array formula that requires CTRL + SHIFT + ENTER
After entering the formula, highlight the cell with the formula and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}


Hope that helps.

 

[Johnny Thunder]

One way would be to concatenate items and then do VLOOKUP like:
Column E:
=CONCATENATE(C1,D1)
Column F:
=CONCATENATE(A1,B1)
Column G:
Normal VLOOKUP i.e. searching F1 in E Column.

Thank you! The formula worked great! Now I just need to add something to it, how would I write a addition to this formula to say: If it matches leave the cell blank if not give me "text" i will fill in the text with something like "Variance"

 

[taurean]

In that case, you will have to modify the formula as below:

[COLOR=blue]=IF(ISERROR(VLOOKUP(F1,E:E,1,0)),"Variance","")[/COLOR]