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

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

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

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

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

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