Reader’s Questions - Performing two-way look up

Clever Excel formulas March 16th, 2009

Question:

I have - Table 1 and Table 2

How can I bring salary information in table 2 from table 1 for corresponding dept and jobcode?
Thanks
Bob Lee
Performing two way look up

Our take on this excel problem

You can join Department and Job Code in both table 1 as well as 2 using concatenate function. And then using vlookup function, you can look up salary from table 1 for a particular department and job code in table 2 as shown below:

Step 1: Concatenate column B and column C

HET two way look up 2

Step 2: Vlookup on the new concatenate column just created
Performing two way look up 3

Hope this helps.

Reader's Comments

Jialin Li | May 29th, 2009 at 4:00 am
there is another way that uses array formula. This method does not require a new column:

sum(($A$4:$A$13=A17)*($B$4:$B$13=B17)*($C$4:$C$13))
Raghav | August 2nd, 2010 at 5:23 pm
HI

Can use this too.

=IF(CONCATENATE($A16,$B16)=CONCATENATE($A3,$B3),$C3,0)

It Works.

Thanks.
Talha | November 29th, 2010 at 7:18 am
Or it could be this formula
=LOOKUP(2,1/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

try this in cell (E17)