Partial match in table = return column header

[tj2419]

I am trying to create a fomula in K2 to search for the value in L2 within the range A1:I100 and if it finds a partial match to return the column heading.

E.g. in this scenario it searches for "7s/DT4" finds a match in B2 and returns B1 e.g. Friday 9th Sep: Period 1

I've tried using INDEX and MATCH formulas with wildcards but just don't seem to be able to get it to work. Any one have any advice?

 

[ExceLoki]

did you put wildcards on both sides of the cell reference?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	staff	Friday 9th sep: period 1	Friday 9th sep: period 2	Friday 9th sep: break	Friday 9th sep: period 3	Friday 9th sep: lunch 1	Friday 9th sep: lunch 2	Friday 9th sep: period 4	Friday 9th sep: period 5			search key
2	mr smith	year 7: 7s/DT4 (2022/2023)	year 11: sw/Ch5 (2022/2023)				year 7: willow 7a (2022/2023)		year 10: 10a/ch7 (2022/2023)		Friday 9th sep: period 1	7s/DT4
Sheet1

Cell Formulas
Range	Formula
K2	K2	=INDEX($B$1:$I$1,MATCH("*"&$L$2&"*",B2:I2,0))

 

[tj2419]

did you put wildcards on both sides of the cell reference?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	staff	Friday 9th sep: period 1	Friday 9th sep: period 2	Friday 9th sep: break	Friday 9th sep: period 3	Friday 9th sep: lunch 1	Friday 9th sep: lunch 2	Friday 9th sep: period 4	Friday 9th sep: period 5			search key
2	mr smith	year 7: 7s/DT4 (2022/2023)	year 11: sw/Ch5 (2022/2023)				year 7: willow 7a (2022/2023)		year 10: 10a/ch7 (2022/2023)		Friday 9th sep: period 1	7s/DT4
Sheet1

Cell Formulas
Range	Formula
K2	K2	=INDEX($B$1:$I$1,MATCH("*"&$L$2&"*",B2:I2,0))

Thanks @ExceLoki I did but looks like not correctly. The formula appears to work for that one row now but is there a way it can check a few rows of data?

I tried changing it to =INDEX($B$1:$I$1,MATCH("*"&$L$2&"*",B2:I20,0)) but it seems to break the formula.

Thanks again for your help.

 

[ExceLoki]

you should be able to drag the formula down into the cells below.
what you have it doing there is searching not row by row, but the entire range to return to the one cell k2.
also are you wanting each row to look for the same search key?

Book1
	A
1	#N/A
2	#N/A
3	#N/A
4	#N/A
5	#N/A
6	#N/A
7	#N/A
8	#N/A
9	#N/A
10	#N/A
11	#N/A
12	#N/A
13	#N/A
14	#N/A
15	#N/A
Sheet7

Cell Formulas
Range	Formula
A1:A15	A1	=INDEX($B$1:$I$1,MATCH("*"&$L$2&"*",B2:I2,0))

 

[tj2419]

There will be about 10 search keys in total but they need to search the entire table rather than a specific row as the value could appear in any of the columns or rows between B2:I10.

 

[ExceLoki]

can you share a larger data set?
i recommend using XL2BB