| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match Problem
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 "Luke M" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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. "MrRJ" wrote: 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 "Luke M" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
Glad I could help, and thanks for the feedback!
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MrRJ" wrote: 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. "MrRJ" wrote: 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 "Luke M" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 "Luke M" wrote: Glad I could help, and thanks for the feedback! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MrRJ" wrote: 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. "MrRJ" wrote: 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 "Luke M" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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" wrote: 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 "Luke M" wrote: Glad I could help, and thanks for the feedback! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MrRJ" wrote: 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. "MrRJ" wrote: 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 "Luke M" wrote: 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match Problem
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" wrote: 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" wrote: 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 "Luke M" wrote: Glad I could help, and thanks for the feedback! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MrRJ" wrote: 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. "MrRJ" wrote: 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 "Luke M" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| INDEX OR Match Problem | Excel Worksheet Functions | |||
| Index/Match problem | Excel Worksheet Functions | |||
| INDEX / MATCH problem | Excel Worksheet Functions | |||
| Possible index/match problem? | Excel Worksheet Functions | |||
| Index Match Problem | Excel Worksheet Functions | |||
Linear Mode
