MATCH and INDEX combination using multiple criteria?

[John]

I'm combining the INDEX function with the MATCH function to do a lookup, but
I have multiple criteria. (I can't use the DGET function because it requires
a two row criteria range, which won't work for my situation). Can multiple
criteria work with the MATCH and INDEX combo? Or is there a different way to
do what I want to do?:

A B C
1 Name Date Amount
2 Joe 1 20.00
3 Joe 3 30.00
4 Joe 7 15.00
5 Ian 1 12.00
6 Ian 3 5.00

If my criteria is Joe, 7, I'd like Excel (2007) to return 15.00. My one
criteria INDEX and MATCH formula looks like this:

=INDEX(A1:C6,MATCH("Joe",A:A,0),3)

But it returns 20.00, the first instance of Joe.
Thanks.

 

[Tom Hutchins]

You can do it easily with SUMPRODUCT or with a SUM(IF( array formula. An
array formula must be entered by pressing CTRL+Shift+Enter and not just
Enter. If you do it correctly then Excel will put curly brackets around the
formula {}. You can't type these yourself. If you edit the formula you must
enter it again with CTRL+Shift+Enter.

The SUMPRODUCT formula:
=SUMPRODUCT(--(A1:A6="Joe"),--(B1:B6=7),C1:C6)

The SUM(IF( array formula:
{=SUM(IF((A1:A6="Joe")*(B1:B6=7),C1:C6,0))}

Bob Phillips explains =sumproduct() in more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps,

Hutch

 

[ryguy7272]

I can think of a couple things:
With 'Joe' in F1 and 'Amount' in F2
F3 =INDEX(A1:C7,MATCH(F1,A1:A7,1),MATCH(F2,A1:C1,0))

Or...
=SUMPRODUCT(--(A1:A7="Joe"),--(B1:B7=7),C1:C7)

HTH,
Ryan--

 

[Bernd P]

Hello,

For one special lookup you got the SUMPRODUCT hint already.

If you want it auto-updating for all your combinations have a look at
my UDF Sfreq, please:
http://www.sulprobil.com/html/sfreq.html

Regards,
Bernd