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

Sl No	Manager	Trainee
1	John
2	Steve	Jeffy
3	Harry	Johnson
4	Johny

<tbody>
</tbody>

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

Sl No	Manager	Trainee
1	John
2	Steve	Jeffy
3	Harry	Johnson
4	Johny

<tbody>
</tbody>

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

	A	B	C
1	john	John	Manager
2		Johnson	Trainee
3		Johny	Manager
4

<tbody>
</tbody>

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.