VLOOKUP using two criteria

J

Josh Johansen

I have a list that I want to use VLOOKUP on, what I need to do is use two
columns to perform the lookup because there are examples where there are
exact matches in the first column and then I need to use the second column to
help select the right value for VLOOKUP. I am trying to use two VLOOKUPS, as
well as use an IF statement but am struggling to make it work. Any
suggestions?
 
B

bj

Please give an example, there are too many possible senerios which fit your
description that would need different answers.
 
J

Josh Johansen

Allright, this is what I have. 3 Columns, Employee Name / Area / Hours

Employee Name / Area / Hours
Anderson / LB / 42
Brady / LC / 36
Johnson / LA / 36
Johnson / LC / 45
Roberts / LB / 24
Roberts / LC / 45
Wang / LA / 43

So what I could do is do a VLOOKUP and get the hours, but when I get Johnson
or Roberts, there is no way to ensure VLOOKUP is returning the value I want.
So what I would like to do is use the area they work in as a second criteria
so if the Name matches, the area will provide the seperation. Thanks!
 
M

Mike H

A glimpse of your data would have helped but try:-

=IF(LOOKUP(E1,A1:A20,A1:A20)>0,VLOOKUP(E2,B1:C20,2,FALSE),"")

Looks in column A1-A20 for a value in E1 and if it finds a match does a
vlookup on Colmn B for the value in E2 and returns cloumn C

Now how wrong was that?

Mike
 
S

squenson

When I have to do a VLOOKUP on two conditions, I always prefer to create a
new column with a concatenation of the two fields, and then I perform the
VLOOKUP. So in your example I would insert a column between Department and
Hours, and have the formula C2: = A2&B2.

Then I would write the lookup =VLOOKUP(<Name>&<Department>, C2:Dxxx,2, 0)
 
J

Josh Johansen

The Concate function seems like it will be perfect for what I am try to do, I
didnt really want to add more columns, but it certainly does what I want to
do and easily, thanks so much!
 
B

bj

sumproduct() would probably do what you want instead of vlookup()

=sumproduct(--(name_range=name),--(area_range = area),Hours_range)
the --( changes the logical true false to a numeric 1 0
the arrays in the ranges must be the same size but unless you are in 2007
they cannot be the shorthand for an entire column A1:A64000 will work, A:A
will not
 
Top