VLOOKUP using two criteria
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
"Josh Johansen" wrote:
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!
"bj" wrote:
Please give an example, there are too many possible senerios which fit your
description that would need different answers.
"Josh Johansen" wrote:
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?
|