Should I use INDEX?

[MrRJ]

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

 

[MrRJ]

I used this formula, it does not work for colum ranges.
=INDEX(A4:A15,MATCH(F18,C4:F15,0))

 

[Peter T]

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]

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]

Try this -

=INDEX(A2:A13,MATCH(1,(B2:B13=H1)+(C2:C13=H1)+(D213=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

 

[Peter T]

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