Index Match Problem

[MrRJ]

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

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?

 

[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

 

[Luke M]

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,D415,0)),0,MATCH(F18,D415,0)),IF(ISERROR(MATCH(F18,E4:E15,0)),0,MATCH(F18,E4:E15,0)),IF(ISERROR(MATCH(F18,F4:F15,0)),0,MATCH(F18,F4:F15,0)),IF(ISERROR(MATCH(F18,G4:G15,0)),0,MATCH(F18,G4:G15,0))))

 

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

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.

 

[MrRJ]

Luke,
It looks much better. One small problem. I would like to reference the
results of the find to come from row A, as illustrated below. Any thoughts?
We are almost there, I really appreciate your help.

MrRJ

 

[MrRJ]

I figured it out!
I made a change to your match formula.
=INDEX($A$3:$A$14,MATCH($F$18,B3:B14,0))

It works like a charm! Thanks.

 

[Luke M]

Glad I could help, and thanks for the feedback!

 

[MrRJ]

Hey Luke,
Can I bother you one more time?

How can I incorporate this formula using two different files?

File A
2 columns would be used. One is where the result is, such as
=MAX(IF(ISNUMBER(B2:G2),B2:G2,0)) and the next column would be the data used
to find it.

File B
Contains the data as illustrated below...A3:F36. How can I use this formula
=INDEX($A$3:$A$14,MATCH($F$18,B3:B14,0)) when the cell (F18) is located in
File A?

I hope you understand where I am coming from? Your help is much appreciated.
This is part of a huge macro that I created. This is the final piece....I
think.

MrRJ

 

[Teethless mama]

Try this:

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

 

[Luke M]

I'm not sure, but I think you're wanting to know how to link to a seperate
file?

Just change the cell reference in original formula to include workbook
reference.
Example:
=INDEX($A$3:$A$14,MATCH('[File A.xls]Sheet1'!$F$18,B3:B14,0))

Easiest way to set this up is to have both workbooks open, begin editing the
formula in File B, then select File A and click the cell you want (F18).

Is this what you were looking for?

 

[MrRJ]

Thanks,
I was able to work it out.

MrRJ

Try this:

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

 

[MrRJ]

Hey Luke,
thanks for your tremendous help. I was able to use the next persons
suggestion. I was also able to link to another file using this formula.

Thanks again,
MrRJ

I'm not sure, but I think you're wanting to know how to link to a seperate
file?

Just change the cell reference in original formula to include workbook
reference.
Example:
=INDEX($A$3:$A$14,MATCH('[File A.xls]Sheet1'!$F$18,B3:B14,0))

Easiest way to set this up is to have both workbooks open, begin editing the
formula in File B, then select File A and click the cell you want (F18).

Is this what you were looking for?
--
Best Regards,

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

Hey Luke,
Can I bother you one more time?

How can I incorporate this formula using two different files?

File A
2 columns would be used. One is where the result is, such as
=MAX(IF(ISNUMBER(B2:G2),B2:G2,0)) and the next column would be the data used
to find it.

File B
Contains the data as illustrated below...A3:F36. How can I use this formula
=INDEX($A$3:$A$14,MATCH($F$18,B3:B14,0)) when the cell (F18) is located in
File A?

I hope you understand where I am coming from? Your help is much appreciated.
This is part of a huge macro that I created. This is the final piece....I
think.

MrRJ