Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I use INDEX?
Good morning,
What method should I use, I was thinking of using INDEX, however, it will not work for me. I can have more than 10 columns and more than 10 rows of data. In my one cell, I would like to have the result from the first column to have returned. In in another cell, I would like to have the result from the second colum, if applicable. All the other columns are just data. For example, if column M on my data sheet has the name Gretzky, then I would like to see in column N the code 25 and column O Rangers. Does this make sense? If there is a name that is not on the list, then leave alone and do not fill in the code. Code Table 1 Alternates Alternates Alternates 25 Rangers Messier Gretzky 26 Devils 27 Yankees Jeter Ruth Mantle 28 Red Sox Yaz Martinez 29 Blue Jays 30 Indians Powell 31 Islanders 32 Blues 33 Reds Rose 34 Angels Carew 35 Lakers Johnson 36 Celtics Bird McHale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I use INDEX?
I used this formula, it does not work for colum ranges.
=INDEX(A4:A15,MATCH(F18,C4:F15,0)) "MrRJ" wrote: Good morning, What method should I use, I was thinking of using INDEX, however, it will not work for me. I can have more than 10 columns and more than 10 rows of data. In my one cell, I would like to have the result from the first column to have returned. In in another cell, I would like to have the result from the second colum, if applicable. All the other columns are just data. For example, if column M on my data sheet has the name Gretzky, then I would like to see in column N the code 25 and column O Rangers. Does this make sense? If there is a name that is not on the list, then leave alone and do not fill in the code. Code Table 1 Alternates Alternates Alternates 25 Rangers Messier Gretzky 26 Devils 27 Yankees Jeter Ruth Mantle 28 Red Sox Yaz Martinez 29 Blue Jays 30 Indians Powell 31 Islanders 32 Blues 33 Reds Rose 34 Angels Carew 35 Lakers Johnson 36 Celtics Bird McHale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I use INDEX?
I don't understand your overall objective but try changing the Match array
to a single column, eg =INDEX(A4:A15,MATCH(F18,C4:c15,0)) Regards, Peter T "MrRJ" wrote in message ... I used this formula, it does not work for colum ranges. =INDEX(A4:A15,MATCH(F18,C4:F15,0)) "MrRJ" wrote: Good morning, What method should I use, I was thinking of using INDEX, however, it will not work for me. I can have more than 10 columns and more than 10 rows of data. In my one cell, I would like to have the result from the first column to have returned. In in another cell, I would like to have the result from the second colum, if applicable. All the other columns are just data. For example, if column M on my data sheet has the name Gretzky, then I would like to see in column N the code 25 and column O Rangers. Does this make sense? If there is a name that is not on the list, then leave alone and do not fill in the code. Code Table 1 Alternates Alternates Alternates 25 Rangers Messier Gretzky 26 Devils 27 Yankees Jeter Ruth Mantle 28 Red Sox Yaz Martinez 29 Blue Jays 30 Indians Powell 31 Islanders 32 Blues 33 Reds Rose 34 Angels Carew 35 Lakers Johnson 36 Celtics Bird McHale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I use INDEX?
Hello Peter,
I am sorry if I have confused you. Basically, I am trying to find a match within muliple columns. What ever my selection is, it will find the match in columns B through F and the result will be from column A. Does that make sense? 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 "Peter T" wrote: I don't understand your overall objective but try changing the Match array to a single column, eg =INDEX(A4:A15,MATCH(F18,C4:c15,0)) Regards, Peter T "MrRJ" wrote in message ... I used this formula, it does not work for colum ranges. =INDEX(A4:A15,MATCH(F18,C4:F15,0)) "MrRJ" wrote: Good morning, What method should I use, I was thinking of using INDEX, however, it will not work for me. I can have more than 10 columns and more than 10 rows of data. In my one cell, I would like to have the result from the first column to have returned. In in another cell, I would like to have the result from the second colum, if applicable. All the other columns are just data. For example, if column M on my data sheet has the name Gretzky, then I would like to see in column N the code 25 and column O Rangers. Does this make sense? If there is a name that is not on the list, then leave alone and do not fill in the code. Code Table 1 Alternates Alternates Alternates 25 Rangers Messier Gretzky 26 Devils 27 Yankees Jeter Ruth Mantle 28 Red Sox Yaz Martinez 29 Blue Jays 30 Indians Powell 31 Islanders 32 Blues 33 Reds Rose 34 Angels Carew 35 Lakers Johnson 36 Celtics Bird McHale |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I use INDEX?
Try this -
=INDEX(A2:A13,MATCH(1,(B2:B13=H1)+(C2:C13=H1)+(D2: D13=H1)+(E2:E13=H1)+(F2:F13=H1),0)) Array enter with Ctrl-Shift-Enter H1 is the cell with the Lookup value Regards, Peter T "MrRJ" wrote in message ... Hello Peter, I am sorry if I have confused you. Basically, I am trying to find a match within muliple columns. What ever my selection is, it will find the match in columns B through F and the result will be from column A. Does that make sense? 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 "Peter T" wrote: I don't understand your overall objective but try changing the Match array to a single column, eg =INDEX(A4:A15,MATCH(F18,C4:c15,0)) Regards, Peter T "MrRJ" wrote in message ... I used this formula, it does not work for colum ranges. =INDEX(A4:A15,MATCH(F18,C4:F15,0)) "MrRJ" wrote: Good morning, What method should I use, I was thinking of using INDEX, however, it will not work for me. I can have more than 10 columns and more than 10 rows of data. In my one cell, I would like to have the result from the first column to have returned. In in another cell, I would like to have the result from the second colum, if applicable. All the other columns are just data. For example, if column M on my data sheet has the name Gretzky, then I would like to see in column N the code 25 and column O Rangers. Does this make sense? If there is a name that is not on the list, then leave alone and do not fill in the code. Code Table 1 Alternates Alternates Alternates 25 Rangers Messier Gretzky 26 Devils 27 Yankees Jeter Ruth Mantle 28 Red Sox Yaz Martinez 29 Blue Jays 30 Indians Powell 31 Islanders 32 Blues 33 Reds Rose 34 Angels Carew 35 Lakers Johnson 36 Celtics Bird McHale |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I use INDEX?
Forgot to mention: the formula assumes similar values, of the value you are
trying to match, will not exist in the same row. If that's a possibility would need to adapt further. Peter T "Peter T" <peter_t@discussions wrote in message ... Try this - =INDEX(A2:A13,MATCH(1,(B2:B13=H1)+(C2:C13=H1)+(D2: D13=H1)+(E2:E13=H1)+(F2:F13=H1),0)) Array enter with Ctrl-Shift-Enter H1 is the cell with the Lookup value Regards, Peter T "MrRJ" wrote in message ... Hello Peter, I am sorry if I have confused you. Basically, I am trying to find a match within muliple columns. What ever my selection is, it will find the match in columns B through F and the result will be from column A. Does that make sense? 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 "Peter T" wrote: I don't understand your overall objective but try changing the Match array to a single column, eg =INDEX(A4:A15,MATCH(F18,C4:c15,0)) Regards, Peter T "MrRJ" wrote in message ... I used this formula, it does not work for colum ranges. =INDEX(A4:A15,MATCH(F18,C4:F15,0)) "MrRJ" wrote: Good morning, What method should I use, I was thinking of using INDEX, however, it will not work for me. I can have more than 10 columns and more than 10 rows of data. In my one cell, I would like to have the result from the first column to have returned. In in another cell, I would like to have the result from the second colum, if applicable. All the other columns are just data. For example, if column M on my data sheet has the name Gretzky, then I would like to see in column N the code 25 and column O Rangers. Does this make sense? If there is a name that is not on the list, then leave alone and do not fill in the code. Code Table 1 Alternates Alternates Alternates 25 Rangers Messier Gretzky 26 Devils 27 Yankees Jeter Ruth Mantle 28 Red Sox Yaz Martinez 29 Blue Jays 30 Indians Powell 31 Islanders 32 Blues 33 Reds Rose 34 Angels Carew 35 Lakers Johnson 36 Celtics Bird McHale |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index help | Excel Discussion (Misc queries) | |||
INDEX | Excel Programming | |||
PivotTable.columnfields(index) vs Pivotcell.columnitem(index) | Excel Programming | |||
Chart axes color index vs font color index | Charts and Charting in Excel | |||
How do I pull the col. index value as well as row index value | Excel Discussion (Misc queries) |