Match & create a column

[hsandeep]

[TABLE="width: 397"]
<TBODY>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FOOTBALL[/TD]
[TD]RUGBY[/TD]
[TD]BASEBALL[/TD]
[TD]HOCKEY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DANIEL[/TD]
[TD][/TD]
[TD]HARRY[/TD]
[TD][/TD]
[TD][/TD]
[TD]RUGBY[/TD]
[/TR]
[TR]
[TD]HARRY[/TD]
[TD]DANIEL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FOOTBALL[/TD]
[/TR]
[TR]
[TD]WILLIAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MICHAEL[/TD]
[TD]HOCKEY[/TD]
[/TR]
[TR]
[TD]MICHAEL[/TD]
[TD][/TD]
[TD][/TD]
[TD]WILLIAM[/TD]
[TD][/TD]
[TD]BASEBALL[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD][/TD]
[TD][/TD]
[TD]CARL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JERRY[/TD]
[TD][/TD]
[TD][/TD]
[TD]JERRY[/TD]
[TD][/TD]
[TD]BASEBALL[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=3><COL></COLGROUP>[/TABLE]

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]

[TABLE="width: 397"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FOOTBALL
[/TD]
[TD]RUGBY
[/TD]
[TD]BASEBALL
[/TD]
[TD]HOCKEY
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DANIEL
[/TD]
[TD][/TD]
[TD]HARRY
[/TD]
[TD][/TD]
[TD][/TD]
[TD]RUGBY
[/TD]
[/TR]
[TR]
[TD]HARRY
[/TD]
[TD]DANIEL
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FOOTBALL
[/TD]
[/TR]
[TR]
[TD]WILLIAM
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MICHAEL
[/TD]
[TD]HOCKEY
[/TD]
[/TR]
[TR]
[TD]MICHAEL
[/TD]
[TD][/TD]
[TD][/TD]
[TD]WILLIAM
[/TD]
[TD][/TD]
[TD]BASEBALL
[/TD]
[/TR]
[TR]
[TD]Carl
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CARL
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JERRY
[/TD]
[TD][/TD]
[TD][/TD]
[TD]JERRY
[/TD]
[TD][/TD]
[TD]BASEBALL
[/TD]
[/TR]
</tbody>[/TABLE]

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?