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

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

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

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

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

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