Match & create a column

[hsandeep]

A	B	C	D	E	F
	FOOTBALL	RUGBY	BASEBALL	HOCKEY
DANIEL		HARRY			RUGBY
HARRY	DANIEL				FOOTBALL
WILLIAM				MICHAEL	HOCKEY
MICHAEL			WILLIAM		BASEBALL
Carl			CARL
JERRY			JERRY		BASEBALL

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=3><COL></COLGROUP>

column A=Input & B1, C1, D1 & E1 is Input. B2:B7, C2;C7, D2:D7 & E2:E7 is to be MATCHED EXACTLY with column A to get column F.
How to accomplish?

 

[Robert Mika]

Excel 2010
	A	B	C	D	E	F
1		FOOTBALL	RUGBY	BASEBALL	HOCKEY
2	DANIEL		HARRY			RUGBY
3	HARRY	DANIEL				FOOTBALL
4	WILLIAM				MICHAEL	HOCKEY
5	MICHAEL			WILLIAM		BASEBALL
6	Carl			CARL
7	JERRY			JERRY		BASEBALL
Sheet3

Cell Formulas
Range	Formula
F2	=IF(AND(UPPER(A2)=UPPER(LOOKUP(2,1/($B2:$E2<>""),B2:E2))=TRUE,EXACT(A2,LOOKUP(2,1/($B2:$E2<>""),B2:E2))=FALSE),"",LOOKUP(2,1/($B2:$E2<>""),$B$1:$E$1))

 

[hsandeep]

It works. Thanks.

 

[hsandeep]

Just a minute!!! In D3 I punched M & in the output cell F3 it shows BASEBALL which is INCORRECT since there is no cell containing M 'ONLY' in A2:A7.

 

[Robert Mika]

Please ignore.
I know what you mean.
Will wotk on this

 

[Robert Mika]

Just a minute!!! In D3 I punched M & in the output cell F3 it shows BASEBALL which is INCORRECT since there is no cell containing M 'ONLY' in A2:A7.

Excel 2010
	A	B	C	D	E	F
1		FOOTBALL	RUGBY	BASEBALL	HOCKEY
2	DANIEL		HARRY			RUGBY
3	HARRY			M
4	WILLIAM				MICHAEL	HOCKEY
5	MICHAEL			WILLIAM		BASEBALL
6	Carl			CARL
7	JERRY			JERRY		BASEBALL
Sheet3

Cell Formulas
Range	Formula
F2	{=IF(SUM(IF(ISNA(EXACT($A$2:$A$7,LOOKUP(2,1/($B2:$E2<>""),$B2:$E2))),"",--EXACT($A$2:$A$7,LOOKUP(2,1/($B2:$E2<>""),$B2:$E2))))=1,LOOKUP(2,1/($B2:$E2<>""),$B$1:$E$1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hsandeep]

It WORKS. Thanks

 

[Aladin Akyurek]

A	B	C	D	E	F
	FOOTBALL	RUGBY	BASEBALL	HOCKEY
DANIEL		HARRY			RUGBY
HARRY	DANIEL				FOOTBALL
WILLIAM				MICHAEL	HOCKEY
MICHAEL			WILLIAM		BASEBALL
Carl			CARL
JERRY			JERRY		BASEBALL

<tbody>
</tbody>

column A=Input & B1, C1, D1 & E1 is Input. B2:B7, C2;C7, D2:D7 & E2:E7 is to be MATCHED EXACTLY with column A to get column F.
How to accomplish?

Shorter in number of functions invoked...

=IF(ISNA(LOOKUP(9.99999999999999E+307,
  FIND(" "&LOOKUP(REPT("z",255),B2:E2)&" "," "&$A$2:$A$7&" "))),"",
   LOOKUP(REPT("z",255),B2:E2,$B$1:$E$1))

If you would define BigNum as referring to:

=9.99999999999999E+307

and BigStr as referring to:

=REPT("z",255)

the formula becomes:

=IF(ISNA(LOOKUP(BigNum,
  FIND(" "&LOOKUP(BigStr,B2:E2)&" "," "&$A$2:$A$7&" "))),"",
   LOOKUP(BigStr,B2:E2,$B$1:$E$1))

 

[Robert Mika]

It WORKS. Thanks

You are welcome but try Aladin's solution as well.

BTW: Where are you getting your questions from?
For some time you have been asking very how to say.. EXACT questions

 

[hsandeep]

Aladin's formula works well. But I am unable to decide which one will be more flexible + reliable?