Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLookup on two criteria - Not two dimensional
Hi,
I'm trying to use Vlookup to select a value based on two criteria but to no avail. E.g. Name Group Date Jon Dev 1/2/2005 Jon Test 6/3/2005 Fred Dev 4/1/2005 Id like to be able to search on Name and Group and return the date. Id normally use SumProduct however as the date is numeric, if there are multiple entries the same it of course sums them€¦.! TIA, Jon C |
#2
|
|||
|
|||
one way to do it if the multiple entries are identical
set up a helper column just befor the date column and use the formula =name & Group Do your vlookup for the concatinated value and it will return the first match "Jon C" wrote: Hi, I'm trying to use Vlookup to select a value based on two criteria but to no avail. E.g. Name Group Date Jon Dev 1/2/2005 Jon Test 6/3/2005 Fred Dev 4/1/2005 Id like to be able to search on Name and Group and return the date. Id normally use SumProduct however as the date is numeric, if there are multiple entries the same it of course sums them€¦.! TIA, Jon C |
#3
|
|||
|
|||
I like your thinking. Thanks BJ.
"bj" wrote: one way to do it if the multiple entries are identical set up a helper column just befor the date column and use the formula =name & Group Do your vlookup for the concatinated value and it will return the first match "Jon C" wrote: Hi, I'm trying to use Vlookup to select a value based on two criteria but to no avail. E.g. Name Group Date Jon Dev 1/2/2005 Jon Test 6/3/2005 Fred Dev 4/1/2005 Id like to be able to search on Name and Group and return the date. Id normally use SumProduct however as the date is numeric, if there are multiple entries the same it of course sums them€¦.! TIA, Jon C |
#4
|
|||
|
|||
One way:
Say your target name and group are listed in E1 and E2. Then =INDEX(C:C,MATCH(E1&E2,A1:A1000&B1:B1000,FALSE)) In article , "Jon C" wrote: Hi, I'm trying to use Vlookup to select a value based on two criteria but to no avail. E.g. Name Group Date Jon Dev 1/2/2005 Jon Test 6/3/2005 Fred Dev 4/1/2005 Id like to be able to search on Name and Group and return the date. Id normally use SumProduct however as the date is numeric, if there are multiple entries the same it of course sums them€¦.! TIA, Jon C |
#5
|
|||
|
|||
Hi,
One possiblility is, =SUMPRODUCT((A2:A4=E2)*(B2:B4=F2)*(C2:C4))/SUMPRODUCT((A2:A4=E2)*(B2:B4=F2)) The formula assumes that columns A, B, and C contain the source data, E and F contain the search criteria, and G would return the output (date). It will account for duplicate entries. Regards, B. R. Ramachandran "Jon C" wrote: Hi, I'm trying to use Vlookup to select a value based on two criteria but to no avail. E.g. Name Group Date Jon Dev 1/2/2005 Jon Test 6/3/2005 Fred Dev 4/1/2005 Id like to be able to search on Name and Group and return the date. Id normally use SumProduct however as the date is numeric, if there are multiple entries the same it of course sums them€¦.! TIA, Jon C |
#6
|
|||
|
|||
I like this syntax:
=index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. Jon C wrote: Hi, I'm trying to use Vlookup to select a value based on two criteria but to no avail. E.g. Name Group Date Jon Dev 1/2/2005 Jon Test 6/3/2005 Fred Dev 4/1/2005 Id like to be able to search on Name and Group and return the date. Id normally use SumProduct however as the date is numeric, if there are multiple entries the same it of course sums them€¦.! TIA, Jon C -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with a vLookup, multiple criteria | Excel Worksheet Functions | |||
Vlookup - double criteria | Excel Worksheet Functions | |||
Vlookup - double criteria | Excel Worksheet Functions | |||
SUMIF using VLOOKUP as criteria | Excel Worksheet Functions | |||
VlookUp with Multiple Criteria? | Excel Worksheet Functions |