Return column header where partial text is found

[RockChalk]

I have multiple categories, each of which has some values. In my sheet, I am trying to find what "category" a specific cell contains. The range would contain the partial text of the cell however.

Basically, my if statement is if e4 contains an "M" then it = v2, if not then check $g$2:$v15 to see if any of those values are matches or partial matches of c4, and if none of them are found, then u2.

I have attempted index:match, if/countifs, I continually get the #N/A result, regardless of my attempt at a formula.

Clearly, I'm new to this:

This is what I'm hoping to yield with the formula (f4:f17) instead of the the *N/A.

 

[DRSteele]

It is achievable.

Please upload that data using the tool listed in my signature below. That way, we can help you without having to type all that in.

 

[RockChalk]

v18offensecodes.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Version 18 Code Order						Offense Categories
2	Offense Code	Literal	Citation	Statute	L/D	Offense Category	Cap Mur	Murder	Oth Hom	Agg Aslt	Sex Aslt	Indecency	Fam Viol	Robbery	Burglary	Theft	Auto Theft	Drug Sale	Drug Poss	Fel DWI	Oth Fel	All Misd
3	03990001	MISREPRESENT CHILD AS FAMILY MEMBER ENTRY PORT	37.082(c)	PC	MB	All Misd	19.03	19.02	19.04	22.01	22.011	22.011	22.01(B)(2)	29.03	30.02	31*	31.03	481	481*	49.04
4	09990017	MANSLAUGHTER	19.04	PC	F2	*Oth Hom			19.05	22.04	22.021	22.021	71.0021(B)	29.02	30.04	32.31	31.07	482	483*	49.045
5	09990018	CRIM NEGLIGENT HOMICIDE	19.05	PC	FS	*Oth Hom			49.08	22.05		21.02	71.003			33A.04		483		49.09
6	09990020	CAPITAL MURDER OF A PEACE OFFICER OR FIREMAN	19.03(a)(1)	PC	FX	*Cap Mur				22.07		21.11	71.005					485		49.07
7	09990021	MURDER UNDER INFLUENCE OF SUDDEN PASSION	19.02(d)	PC	F2	*Mur				22.08		21.12
8	09990022	CAPITAL MURDER BY TERROR THREAT/OTHER FELONY	19.03(a)(2)	PC	FX	#N/A				22.015
9	09990023	CAPITAL MURDER WHILE REMUNERATION	19.03(a)(3)	PC	FX	#N/A				22.02
10	09990024	CAPITAL MURDER PERSON WHILE ESCAPING/ATTEMPT	19.03(a)(4)	PC	FX	#N/A				22.041
11	09990025	CAPITAL MURDER OF ANOTHER PERSON IN PRISON	19.03(a)(5-6)	PC	FX	#N/A				22.09
12	09990026	CAPITAL MURDER OF MULTIPLE PERSONS	19.03(a)(7)	PC	FX	#N/A				22.11
13	09990029	CAPITAL MURDER OF PERSON UNDER TEN YOA	19.03(a)(8)	PC	FX	#N/A				15.01
14	09990030	MURDER	19.02(c)	PC	F1	#N/A				19.02
15	09990031	CAPITAL MURDER OF PERSON =>10YOA<15YOA	19.03(a)(9)	PC	FX	#N/A				19.03
16	09990032	CAPITAL MURDER FOR RETALIATION JUDGE/JUSTICE	19.03(a)(10)	PC	FX	#N/A
17	10050001	UNLAWFUL RESTRAINT LESS THAN 17 YRS OF AGE	20.02(c)(1)	PC	FS	#N/A
18	10990001	KIDNAPPING	20.03(a)	PC	F3	*Oth Fel
19	10990002	AGREEMENT TO ABDUCT FROM CUSTODY	25.031	PC	FS	#N/A
20	10990007	AGG KIDNAPPING FOR RANSOM/REWARD	20.04(a)(1)	PC	F1	#N/A
21	10990008	AGG KIDNAPPING FOR RANSOM/REWARD SAFE RELEASE	20.04(d)	PC	F2	#N/A
22	10990009	AGG KIDNAPPING USE AS SHIELD/HOSTAGE	20.04(a)(2)	PC	F1	#N/A
23	10990010	AGG KIDNAPPING USE AS SHIELD/HOSTAGE SAFE REL	20.04(d)	PC	F2	#N/A
24	10990011	AGG KIDNAPPING FACILITATE	20.04(a)(3)	PC	F1	#N/A
25	10990012	AGG KIDNAPPING FACILITATE SAFE RELEASE	20.04(d)	PC	F2
26	10990015	AGG KIDNAPPING TERRORIZE	20.04(a)(5)	PC	F1
27	10990016	AGG KIDNAPPING TERRORIZE SAFE RELEASE	20.04(d)	PC	F2
28	10990017	AGG KIDNAPPING INTERFERE PERFORMANCE	20.04(a)(6)	PC	F1
29	10990018	AGG KIDNAPPING INTERFERE PERFORMANCE SAFE REL	20.04(d)	PC	F2
30	10990019	UNLAWFUL RESTRAINT	20.02(c)	PC	MA
31	10990020	UNLAWFUL RESTRAINT:EXPOSE TO SBI	20.02(c)(2)(A)	PC	F3
32	10990021	AGG KIDNAPPING W/ DEADLY WEAPON	20.04(b)	PC	F1
33	10990022	AGG KIDNAPPING W/ DEADLY WEAPON SAFE RELEASE	20.04(d)	PC	F2
34	10990023	AGG KIDNAPPING SEXUAL ABUSE	20.04(a)(4)	PC	F1
35	10990024	AGG KIDNAPPING SEXUAL ABUSE SAFE RELEASE	20.04(d)	PC	F2
36	10990025	AGG KIDNAPPING BOD INJURY	20.04(a)(4)	PC	F1
37	10990026	AGG KIDNAPPING BOD INJURY ABUSE SAFE RELEASE	20.04(d)	PC	F2
38	10999999	KIDNAPPING CLASS C	CLASS C	X	MC
39	11990001	SEXUAL ASSLT	22.011(a)(1)	PC	F2
Table 1

Cell Formulas
Range	Formula
F3,F19:F24,F8:F17	F3	=IF(COUNTIF(E3,"*M*"),$V$2,INDEX($G$2:$V$15,MATCH(C3&"*",$G$2:$V$15,-1)))

Named Ranges
Name	Refers To	Cells
Agg_Aslt	='Table 1'!$J$3:$J$15	F3, F19:F24, F8:F17
Auto_Theft	='Table 1'!$Q$3:$Q$4	F3, F19:F24, F8:F17
Burglary	='Table 1'!$O$3:$O$4	F3, F19:F24, F8:F17
Cap_Mur	='Table 1'!$G$3	F3, F19:F24, F8:F17
Drug_Poss	='Table 1'!$S$3:$S$4	F3, F19:F24, F8:F17
Drug_Sale	='Table 1'!$R$3:$R$6	F3, F19:F24, F8:F17
Fam_Viol	='Table 1'!$M$3:$M$6	F3, F19:F24, F8:F17
Fel_DWI	='Table 1'!$T$3:$T$6	F3, F19:F24, F8:F17
Indecency	='Table 1'!$L$3:$L$7	F3, F19:F24, F8:F17
Murder	='Table 1'!$H$3	F3, F19:F24, F8:F17
Oth_Hom	='Table 1'!$I$3:$I$5	F3, F19:F24, F8:F17
Robbery	='Table 1'!$N$3:$N$4	F3, F19:F24, F8:F17
Sex_Aslt	='Table 1'!$K$3:$K$4	F3, F19:F24, F8:F17
Theft	='Table 1'!$P$3:$P$5	F3, F19:F24, F8:F17

 

[RockChalk]

DR, I manually entered some values in column F (with an *) to show you how they should read.
One thing that I did not add in this particular formula was the last value if everything is false, which should be "Oth Fel", so I do know that not all cells will return a value. Thanks,