Multi-condition vlookup
 

I've seen several posts for multi-condition vlookups when data is in
sequential columns but it wasn't clear how to accomplish the same thing when
you need to index a column not adjacent to the columns you are testing. For
example using the following:

Year Model Company a b c e
2005 Red a 10
2005 Green b 100
2004 Green c 50
2005 Red a 20
2002 Yellow a 10

How would you right a vlookup that only searched for 2005, Red, Company A
and returned 20 from colum e?

Thanks in advance.

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(D1:D100,MATCH(1(A1:A100=2005)*(B1:B100="Red ")*(C1:C100="A"),0))

You might also be able to use the non-array entered Sumproduct function
since the returned value is numeric:

=SUMPRODUCT(--(A1:A100=2005),--(B1:B100="Red"),--(C1:C100="A"),D1:D100)

Biff

"Hobbeson" wrote in message
...
I've seen several posts for multi-condition vlookups when data is in
sequential columns but it wasn't clear how to accomplish the same thing
when
you need to index a column not adjacent to the columns you are testing.
For
example using the following:

Year Model Company a b c e
2005 Red a 10
2005 Green b 100
2004 Green c 50
2005 Red a 20
2002 Yellow a 10

How would you right a vlookup that only searched for 2005, Red, Company A
and returned 20 from colum e?

Thanks in advance.

Oops. A lost comma!

=INDEX(D1:D100,MATCH(1,(A1:A100=2005)*(B1:B100="Re d")*(C1:C100="A"),0))

The OP may want to consider using a pivottable, too.

To read more about the pivottable stuff, you may want to look at some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

Biff wrote:

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(D1:D100,MATCH(1(A1:A100=2005)*(B1:B100="Red ")*(C1:C100="A"),0))

You might also be able to use the non-array entered Sumproduct function
since the returned value is numeric:

=SUMPRODUCT(--(A1:A100=2005),--(B1:B100="Red"),--(C1:C100="A"),D1:D100)

Biff

"Hobbeson" wrote in message
...
I've seen several posts for multi-condition vlookups when data is in
sequential columns but it wasn't clear how to accomplish the same thing
when
you need to index a column not adjacent to the columns you are testing.
For
example using the following:

Year Model Company a b c e
2005 Red a 10
2005 Green b 100
2004 Green c 50
2005 Red a 20
2002 Yellow a 10

How would you right a vlookup that only searched for 2005, Red, Company A
and returned 20 from colum e?

Thanks in advance.


--

Dave Peterson

Ooops!

I see there are dupe criteria that equal 2005 "Red" "A".....so:

How do you determine which instance of matching criteria you want returned?

This array formula will return the LAST instance:

=INDEX(D1:D100,LARGE((A1:A100=2005)*(B1:B100="Red" )*(C1:C100="a")*(ROW(A1:A100)),1))

Also, since there are duplicate matching criteria, the Sumproduct will not
work.

I need to slow down and read more carefully!

Biff

"Biff" wrote in message
...
Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(D1:D100,MATCH(1(A1:A100=2005)*(B1:B100="Red ")*(C1:C100="A"),0))

You might also be able to use the non-array entered Sumproduct function
since the returned value is numeric:

=SUMPRODUCT(--(A1:A100=2005),--(B1:B100="Red"),--(C1:C100="A"),D1:D100)

Biff

"Hobbeson" wrote in message
...
I've seen several posts for multi-condition vlookups when data is in
sequential columns but it wasn't clear how to accomplish the same thing
when
you need to index a column not adjacent to the columns you are testing.
For
example using the following:

Year Model Company a b c e
2005 Red a 10
2005 Green b 100
2004 Green c 50
2005 Red a 20
2002 Yellow a 10

How would you right a vlookup that only searched for 2005, Red, Company A
and returned 20 from colum e?

Thanks in advance.

Maybe the OP had a typo, and he wants 30 instead of 20.<g

Then ... *only* the Sumproduct would suffice.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Biff" wrote in message
...
Ooops!

I see there are dupe criteria that equal 2005 "Red" "A".....so:

How do you determine which instance of matching criteria you want
returned?

This array formula will return the LAST instance:


=INDEX(D1:D100,LARGE((A1:A100=2005)*(B1:B100="Red" )*(C1:C100="a")*(ROW(A1:A1
00)),1))

Also, since there are duplicate matching criteria, the Sumproduct will not
work.

I need to slow down and read more carefully!

Biff

"Biff" wrote in message
...
Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(D1:D100,MATCH(1(A1:A100=2005)*(B1:B100="Red ")*(C1:C100="A"),0))

You might also be able to use the non-array entered Sumproduct function
since the returned value is numeric:

=SUMPRODUCT(--(A1:A100=2005),--(B1:B100="Red"),--(C1:C100="A"),D1:D100)

Biff

"Hobbeson" wrote in message
...
I've seen several posts for multi-condition vlookups when data is in
sequential columns but it wasn't clear how to accomplish the same thing
when
you need to index a column not adjacent to the columns you are testing.
For
example using the following:

Year Model Company a b c e
2005 Red a 10
2005 Green b 100
2004 Green c 50
2005 Red a 20
2002 Yellow a 10

How would you right a vlookup that only searched for 2005, Red, Company
A
and returned 20 from colum e?

Thanks in advance.