search a text and return the column head

[srkreignz]

Hi,

I would like to search for a text in a table and return the column head.

Eg: Table A!:C5

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sl No

[/TD]
[TD] Manager[/TD]
[TD]Trainee[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Steve [/TD]
[TD]Jeffy[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Harry[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Johny[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I wud like to search for john(lower case) in say D2 and get Manger in E2.

Tried the formula
=INDEX(A1:C5,1,MATCH("*"&D2&"*",A2:C5,0))
But not working. Please help !!

 

[Andrew Poulsom]

Given your sample data what would you expect the formula to return?

 

[srkreignz]

@andrew I want manager to be returned in E2 against the entered text in (D2)

 

[Andrew Poulsom]

So what specifically should be in E2 given that D2 contains john? Note that MATCH expects a single row or column of data for the lookup array.

 

[srkreignz]

@andrew what I am trying to do is to list the designation of all johns in the table.

 

[Andrew Poulsom]

That doesn't answer my question. Please post the results you expect.

 

[srkreignz]

Table A!:C5, sheet 1


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Sl No
[/TD]
[TD]Manager[/TD]
[TD]Trainee[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Steve[/TD]
[TD]Jeffy[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Harry[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]4[/TD]
[TD="bgcolor: #FAFAFA"]Johny[/TD]
[/TR]
</tbody>[/TABLE]



in Sheet 2, Cell A1 , when i type john(not case sensitive), i want all the johns (John, johnson and Johny) listed in column B with their respective designations ( Manager,Trainee, Manager) in column C

 

[Andrew Poulsom]

Please post the results you expect in sheet 2.

 

[srkreignz]

@andrews,
the output i want in sheet 2 is

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]john[/TD]
[TD]John[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Johnson[/TD]
[TD]Trainee[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Johny[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and thank you for your patience andrews

 

[Andrew Poulsom]

To keep the formulas short I used a helper column (formulas in F1:H1 copied down):

Excel 2010
	A	B	C	D	E	F	G	H
1	Sl No	Manager	Trainee		john	1	John	Manager
2	1	John				3	Johnson	Trainee
3	2	Steve	Jeffy			4	Johny	Manager
4	3	Harry	Johnson
5	4	Johny
Sheet1

Cell Formulas
Range	Formula
G1	=IF(ISNUMBER(F1),INDEX($B$2:$C$5,F1,MATCH("*"&$E$1&"*",INDEX($B$2:$C$5,F1,0),FALSE)),"")
H1	=IF(ISNUMBER(F1),INDEX($B$1:$C$1,MATCH("*"&$E$1&"*",INDEX($B$2:$C$5,F1,0),FALSE)),"")
F1	{=IFERROR(SMALL(IF(ISNUMBER(SEARCH("*"&$E$1&"*",$B$2:$C$5)),ROW($B$2:$C$5)-ROW($B$2)+1),ROWS(F$1:F1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.