Match and Lookup problems

[Titanus]

Okay, so here's my dilemma (I've solved it using an UGLY series of
IF/AND functions):

I have a row of 10 cells, each with a random number from 1-10
(non-repeating and not in order). On top of these cells is a header,
with a two-letter designation defining the column. Next to it I have
ANOTHER set of 10 cells, each with a random number of 1-10
(non-repeating and not in order). The header for Group 2 is identical
to Group 1 (so if A1="A", B1="B", etc. then K1="A", L1="B", etc.).

What I want to do is compare the values in the cells of Group 1 to
Group 2. Basically, I'm looking for which column has a #1 in Group 1,
compare it to the column that has a #1 in Group 2, and if the column
headings are the same, return THAT value. So if C2=1 (so the Column
header is "C") and if M1=1 (this column header is ALSO "C") then I
return the value of "C" (nothing if there is no match). Like I said,
the numbers are unique, so there won't be any recursivity (if that's
not a word, I'm inventing it!).

Thanks for the help.

-Mr. T

 

[Titanus]

Oh, I'm looking to write a function that isn't so ugly. And right now
I had to use TWO cells to do it (since Excel only allows seven
functions at a time and I need 10 IF/AND statements to do the process).
Any help is appreciated.

 

[Biff]

Hi!

Try something like this:

=IF(INDEX(A1:J1,MATCH(1,A2:J2,0))=INDEX(K1:T1,MATCH(1,K2:T2,0)),INDEX(A1:J1,MATCH(1,A2:J2,0)),"")

Biff

 

[Titanus]

Biff, you da man!!

Thank you very much, it worked perfectly! I hadn't thought of using
INDEX.

-Mr. T

 

[Biff]

You're welcome. Thanks for the feedback!

Biff