Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using two criteria
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using two criteria
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using two criteria
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using two criteria
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 "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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using two criteria
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using two criteria
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! "squenson" wrote: 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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP or IF with many criteria | Excel Worksheet Functions | |||
vlookup with two criteria | Excel Worksheet Functions | |||
vlookup with two criteria | Excel Worksheet Functions | |||
VLOOKUP with MAX criteria | Excel Discussion (Misc queries) | |||
two criteria in a vlookup | Excel Worksheet Functions |