ISNUMBER with INDEX MATCH?

[giah98]

I am trying to figure out how to get the right formula to output the column header i need. I tried isnumber with index match and i'm getting n/a or value errors.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]US1[/TD]
[TD]US2[/TD]
[TD]US3[/TD]
[TD]US4[/TD]
[TD]US5[/TD]
[TD]US6[/TD]
[TD]US7[/TD]
[TD]US8[/TD]
[TD]US9[/TD]
[TD]US10[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]456[/TD]
[TD]SERV678[/TD]
[TD][/TD]
[TD]542[/TD]
[TD]369[/TD]
[TD][/TD]
[TD]87[/TD]
[TD]654879[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]

I need to find the cell the has "SERV" and then output the column that it is located. For instance here would b e US3.

Thanks in advanced!!

 

[Fluff]

Hi & welcome to MrExcel.

maybe
=INDEX(A1:J1,MATCH("SERV*",A2:J2,0))

 

[giah98]

Thanks for the quick response!
That just give me a 0 if it's found a "SERV". but I need it to find "SERV" then output the column it's in.

 

[Fluff]

This is what I get

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	US1	US2	US3	US4	US5	US6	US7	US8	US9	US10
2	123	456	SERV678		542	369		87	654879	14
3
4	US3
Sheet3

Cell Formulas
Range	Formula
A4	=INDEX(A1:J1,MATCH("SERV*",A2:J2,0))

 

[giah98]

That works! Thank you! Also I have 2500 lines an it won't auto fill since it references the previous row. Anyway to go about this?

 

[Fluff]

In that case use
=INDEX(A$1:J$1,MATCH("SERV*",A2:J2,0))

 

[giah98]

Awesome! Thanks!!!!

 

[Fluff]

You're welcome