Hi,

I created a formula that does not work for me. What did I do wrong?

=INDEX(B4:B15,MATCH(F18,C4:G15,0))

Thanks,
MrRJ

The array in the MATCH function has to be one-dimensional; (part of) one row
or one column.

What is the formula supposed to do? What are the input values? What result
did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"MrRJ" wrote in message
...
Hi,

I created a formula that does not work for me. What did I do wrong?

=INDEX(B4:B15,MATCH(F18,C4:G15,0))

Thanks,
MrRJ

Niek,
I was afraid of that, being that it has to be one dimensional. I would like
to be more than one dimensional. Here is what I like to accomplish. This
just an example. If I selected "Gretzky", then my return value should be 25.
If I selected "Rice", then my return value should be 28. etc. Hope this is
clear for you.
1 A B C D E F
2 Code Table 1 Alternates Alternates Alternates Alternates
3 25 Rangers Messier Gretzky
4 26 Devils
5 27 Yankees Jeter Ruth Mantle
6 28 Red Sox Yaz Martinez Rice Lynn
7 29 Blue Jays
8 30 Indians Powell
9 31 Islanders
10 32 Blues
11 33 Reds Rose Morgan Bench
12 34 Angels Carew
13 35 Lakers Johnson
14 36 Celtics Bird McHale Parrish


"Niek Otten" wrote:

The array in the MATCH function has to be one-dimensional; (part of) one row
or one column.

What is the formula supposed to do? What are the input values? What result
did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"MrRJ" wrote in message
...
Hi,

I created a formula that does not work for me. What did I do wrong?

=INDEX(B4:B15,MATCH(F18,C4:G15,0))

Thanks,
MrRJ

Bulky, but this will work as long as the value exists in your table.
=INDEX(B4:B15,MAX(IF(ISERROR(MATCH(F18,C4:C15,0)), 0,MATCH(F18,C4:C15,0)),IF(ISERROR(MATCH(F18,D4:D15 ,0)),0,MATCH(F18,D4:D15,0)),IF(ISERROR(MATCH(F18,E 4:E15,0)),0,MATCH(F18,E4:E15,0)),IF(ISERROR(MATCH( F18,F4:F15,0)),0,MATCH(F18,F4:F15,0)),IF(ISERROR(M ATCH(F18,G4:G15,0)),0,MATCH(F18,G4:G15,0))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MrRJ" wrote:

Niek,
I was afraid of that, being that it has to be one dimensional. I would like
to be more than one dimensional. Here is what I like to accomplish. This
just an example. If I selected "Gretzky", then my return value should be 25.
If I selected "Rice", then my return value should be 28. etc. Hope this is
clear for you.
1 A B C D E F
2 Code Table 1 Alternates Alternates Alternates Alternates
3 25 Rangers Messier Gretzky
4 26 Devils
5 27 Yankees Jeter Ruth Mantle
6 28 Red Sox Yaz Martinez Rice Lynn
7 29 Blue Jays
8 30 Indians Powell
9 31 Islanders
10 32 Blues
11 33 Reds Rose Morgan Bench
12 34 Angels Carew
13 35 Lakers Johnson
14 36 Celtics Bird McHale Parrish


"Niek Otten" wrote:

The array in the MATCH function has to be one-dimensional; (part of) one row
or one column.

What is the formula supposed to do? What are the input values? What result
did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"MrRJ" wrote in message
...
Hi,

I created a formula that does not work for me. What did I do wrong?

=INDEX(B4:B15,MATCH(F18,C4:G15,0))

Thanks,
MrRJ

Luke,
Your right, it is bulky. I will try it. Is there is a limit to the number
of columns that I can use? Is there an alternate formula that would do this
trick?

MrRJ

"Luke M" wrote:

Bulky, but this will work as long as the value exists in your table.
=INDEX(B4:B15,MAX(IF(ISERROR(MATCH(F18,C4:C15,0)), 0,MATCH(F18,C4:C15,0)),IF(ISERROR(MATCH(F18,D4:D15 ,0)),0,MATCH(F18,D4:D15,0)),IF(ISERROR(MATCH(F18,E 4:E15,0)),0,MATCH(F18,E4:E15,0)),IF(ISERROR(MATCH( F18,F4:F15,0)),0,MATCH(F18,F4:F15,0)),IF(ISERROR(M ATCH(F18,G4:G15,0)),0,MATCH(F18,G4:G15,0))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MrRJ" wrote:

Niek,
I was afraid of that, being that it has to be one dimensional. I would like
to be more than one dimensional. Here is what I like to accomplish. This
just an example. If I selected "Gretzky", then my return value should be 25.
If I selected "Rice", then my return value should be 28. etc. Hope this is
clear for you.
1 A B C D E F
2 Code Table 1 Alternates Alternates Alternates Alternates
3 25 Rangers Messier Gretzky
4 26 Devils
5 27 Yankees Jeter Ruth Mantle
6 28 Red Sox Yaz Martinez Rice Lynn
7 29 Blue Jays
8 30 Indians Powell
9 31 Islanders
10 32 Blues
11 33 Reds Rose Morgan Bench
12 34 Angels Carew
13 35 Lakers Johnson
14 36 Celtics Bird McHale Parrish


"Niek Otten" wrote:

The array in the MATCH function has to be one-dimensional; (part of) one row
or one column.

What is the formula supposed to do? What are the input values? What result
did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"MrRJ" wrote in message
...
Hi,

I created a formula that does not work for me. What did I do wrong?

=INDEX(B4:B15,MATCH(F18,C4:G15,0))

Thanks,
MrRJ

30 columns would be the max, using current setup.

Workaround, is to create a helper row, (say, row 3), then put this
=MATCH($F$18,C4:C15,0)
into row C and copy across.
Then, in where ever you want the result, put
=MAX(IF(ISNUMBER(C3:G3),C3:G3,0))
entered as an array (Ctrl+Shift+Enter)

This way you have smaller formulas, easier to troubleshoot, it just doesn't
all fit into one cell.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MrRJ" wrote:

Luke,
Your right, it is bulky. I will try it. Is there is a limit to the number
of columns that I can use? Is there an alternate formula that would do this
trick?

MrRJ

"Luke M" wrote:

Bulky, but this will work as long as the value exists in your table.
=INDEX(B4:B15,MAX(IF(ISERROR(MATCH(F18,C4:C15,0)), 0,MATCH(F18,C4:C15,0)),IF(ISERROR(MATCH(F18,D4:D15 ,0)),0,MATCH(F18,D4:D15,0)),IF(ISERROR(MATCH(F18,E 4:E15,0)),0,MATCH(F18,E4:E15,0)),IF(ISERROR(MATCH( F18,F4:F15,0)),0,MATCH(F18,F4:F15,0)),IF(ISERROR(M ATCH(F18,G4:G15,0)),0,MATCH(F18,G4:G15,0))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MrRJ" wrote:

Niek,
I was afraid of that, being that it has to be one dimensional. I would like
to be more than one dimensional. Here is what I like to accomplish. This
just an example. If I selected "Gretzky", then my return value should be 25.
If I selected "Rice", then my return value should be 28. etc. Hope this is
clear for you.
1 A B C D E F
2 Code Table 1 Alternates Alternates Alternates Alternates
3 25 Rangers Messier Gretzky
4 26 Devils
5 27 Yankees Jeter Ruth Mantle
6 28 Red Sox Yaz Martinez Rice Lynn
7 29 Blue Jays
8 30 Indians Powell
9 31 Islanders
10 32 Blues
11 33 Reds Rose Morgan Bench
12 34 Angels Carew
13 35 Lakers Johnson
14 36 Celtics Bird McHale Parrish


"Niek Otten" wrote:

The array in the MATCH function has to be one-dimensional; (part of) one row
or one column.

What is the formula supposed to do? What are the input values? What result
did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"MrRJ" wrote in message
...
Hi,

I created a formula that does not work for me. What did I do wrong?

=INDEX(B4:B15,MATCH(F18,C4:G15,0))

Thanks,
MrRJ

Try this:

=INDEX(B4:B15,MAX(INDEX((C4:G15=F18)*ROW(C4:G15),) )-ROW(C3))



"MrRJ" wrote:

Hi,

I created a formula that does not work for me. What did I do wrong?

=INDEX(B4:B15,MATCH(F18,C4:G15,0))

Thanks,
MrRJ

Thanks,
I was able to work it out.

MrRJ

"Teethless mama" wrote:

Try this:

=INDEX(B4:B15,MAX(INDEX((C4:G15=F18)*ROW(C4:G15),) )-ROW(C3))



"MrRJ" wrote:

Hi,

I created a formula that does not work for me. What did I do wrong?

=INDEX(B4:B15,MATCH(F18,C4:G15,0))

Thanks,
MrRJ