Index Match Problem

  • Thread starter Thread starter MrRJ
  • Start date Start date
M

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
 
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?
 
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
 
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,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))))
 
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
 
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.
 
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
 
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.
 
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
 
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?
 
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

Luke M said:
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!*


MrRJ said:
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
Click to expand...
Click to expand...
 
Back
Top