I'm trying to search partial text in a range of cells and get back a corresponding cell to where the match was found.

[MrPickles79]

I'm trying to search partial text in a range of cells and get back a corresponding cell to where the match was found.. I can get formula to work for exact matches but in my range I have cells with partial text or multiple text options and that is were I get stuck. =CELL("contents",INDEX(J:J,MATCH(B2,T:T,0))),,

		Subtext	Counted Date	UOM	SKUDesc	UPCS
need to search for a partial match		18368​	44978​	CT	QUICK-STOR-LETTER/LEGAL SIZE	["10077511007894"]
720918059005​		19526​	45092​	EA	BINDER PENCIL POUCH BLK/CLEAR	["50024591670249"]
what cell in column C corresponds to match from column G		19526​	44972​	EA	BINDER PENCIL POUCH BLK/CLEAR	["024591670244"]
		31307​	45050​	PK	BIC ROUNDSTIC BP MED BLUE 60	["00070330166998", "0300002212"]
		40663​	45113​	CT	LIN 2826 CASE PAPER	["723382282610"]
		40684​	44991​	CT	LIN 5900N CASE PAPER	["50720918059000", "720918059005"]
		40686​	45152​	CT	LIN 5887N CASE PAPER	["50720918058874", "720918058879"]

 

[JamesCanale]

THis will find all of the 'subtext' column that have A3 anywhere in UPCS.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G
1			Subtext	Counted Date	UOM	SKUDesc	UPCS
2	need to search for a partial match		18368	44978	CT	QUICK-STOR-LETTER/LEGAL SIZE	["10077511007894"]
3	720918059005		19526	45092	EA	BINDER PENCIL POUCH BLK/CLEAR	["50024591670249"]
4	what cell in column C corresponds to match from column G		19526	44972	EA	BINDER PENCIL POUCH BLK/CLEAR	["024591670244"]
5	40684		31307	45050	PK	BIC ROUNDSTIC BP MED BLUE 60	["00070330166998", "0300002212"]
6			40663	45113	CT	LIN 2826 CASE PAPER	["723382282610"]
7			40684	44991	CT	LIN 5900N CASE PAPER	["50720918059000", "720918059005"]
8			40686	45152	CT	LIN 5887N CASE PAPER	["50720918058874", "720918058879"]
Sheet7

Cell Formulas
Range	Formula
A5	A5	=FILTER($C$2:$C$8,ISNUMBER(SEARCH(A3,$G$2:$G$8)))

 

[MrPickles79]

That worked, thank you for the help (I have been trying to find something for almost a week now to work).