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.
|