INDEX MATCH #N/A Error
 

I am trying to figure out a formula to return a value from an array based on
two criteria (one is the row label--the other is the column label) which I
set up on Validation lists). In the end, I would like to be able to choose
the "Style" then the "Wood" type and have the formula return the price to me.
So using the data below, apple/Wood1 would return $22.00.

Can someone point me in the direction of my mistake?

The formula I have been trying to use is below and I get a #N/A error.

=INDEX(b5..d7, MATCH(apple, b5..d7,), MATCH(Wood1, b5..b7,))

A B C D
1 Style= apple
2 Wood= Wood1
3
4 Style Wood1 Wood2
5 apple $22.00 $29.00
6 banana $24.00 $31.00
7 carrot $26.00 $33.00

INDEX MATCH #N/A Error
 

Criterias
C1: holds criteria1
C2: holds criteria2

Create define names:
Wood1: C5:C7
Wood2: D5:D7
apple: C5:D5
banana: C6:D6
carrot: C7:D7

In D2: =INDIRECT(C1) INDIRECT(C2)


"wkjgmom" wrote:

I am trying to figure out a formula to return a value from an array based on
two criteria (one is the row label--the other is the column label) which I
set up on Validation lists). In the end, I would like to be able to choose
the "Style" then the "Wood" type and have the formula return the price to me.
So using the data below, apple/Wood1 would return $22.00.

Can someone point me in the direction of my mistake?

The formula I have been trying to use is below and I get a #N/A error.

=INDEX(b5..d7, MATCH(apple, b5..d7,), MATCH(Wood1, b5..b7,))

A B C D
1 Style= apple
2 Wood= Wood1
3
4 Style Wood1 Wood2
5 apple $22.00 $29.00
6 banana $24.00 $31.00
7 carrot $26.00 $33.00

INDEX MATCH #N/A Error
 

Thank you for the quick reply. I did this and am now getting an #REF! error.
I must have messed it up somewhere so I'm going to try it again. Follow up
question though, does it matter that C1 is a number formatted as text in my
real data?

"Teethless mama" wrote:

Criterias
C1: holds criteria1
C2: holds criteria2

Create define names:
Wood1: C5:C7
Wood2: D5:D7
apple: C5:D5
banana: C6:D6
carrot: C7:D7

In D2: =INDIRECT(C1) INDIRECT(C2)


"wkjgmom" wrote:

I am trying to figure out a formula to return a value from an array based on
two criteria (one is the row label--the other is the column label) which I
set up on Validation lists). In the end, I would like to be able to choose
the "Style" then the "Wood" type and have the formula return the price to me.
So using the data below, apple/Wood1 would return $22.00.

Can someone point me in the direction of my mistake?

The formula I have been trying to use is below and I get a #N/A error.

=INDEX(b5..d7, MATCH(apple, b5..d7,), MATCH(Wood1, b5..b7,))

A B C D
1 Style= apple
2 Wood= Wood1
3
4 Style Wood1 Wood2
5 apple $22.00 $29.00
6 banana $24.00 $31.00
7 carrot $26.00 $33.00

INDEX MATCH #N/A Error
 

Try this:

C1 = apple
C2 = wood1

=INDEX(C5:D7, MATCH(C1, B5:B7,0), MATCH(C2, C4:D4,0))

--
Biff
Microsoft Excel MVP


"wkjgmom" wrote in message
...
I am trying to figure out a formula to return a value from an array based
on
two criteria (one is the row label--the other is the column label) which I
set up on Validation lists). In the end, I would like to be able to
choose
the "Style" then the "Wood" type and have the formula return the price to
me.
So using the data below, apple/Wood1 would return $22.00.

Can someone point me in the direction of my mistake?

The formula I have been trying to use is below and I get a #N/A error.

=INDEX(b5..d7, MATCH(apple, b5..d7,), MATCH(Wood1, b5..b7,))

A B C D
1 Style= apple
2 Wood= Wood1
3
4 Style Wood1 Wood2
5 apple $22.00 $29.00
6 banana $24.00 $31.00
7 carrot $26.00 $33.00