MATCH and INDEX combination using multiple criteria?
 

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.

MATCH and INDEX combination using multiple criteria?
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps,

Hutch

"John" wrote:

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.

MATCH and INDEX combination using multiple criteria?
 

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Tom Hutchins" wrote:

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps,

Hutch

"John" wrote:

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.

MATCH and INDEX combination using multiple criteria?
 

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