VLOOKUP using two criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Please give an example, there are too many possible senerios which fit your
description that would need different answers.
 
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!
 
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
 
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)
 
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!
 
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
 
Back
Top