String match multiple concatinated word lists?

[JJenkx]

I have been building word lists to use for "smart" color coding. My previous word list formula works well (Doesn't match below table) "SUMPRODUCT(--(NOT(ISERR(SEARCH($J$4:$J$120,'Work Orders'!$E1)))))>0".

I tried adding concatenate function into my formula "SUMPRODUCT(--(NOT(ISERR(SEARCH(****CONCATINATE(------LIST1------$J$4:$J$120------LIST2****..........." but I am getting bad results. I am new to excel and no other coding experience. Is there a formula to search string matches using string concatenation?


Below is an example and I have color coded the answers that I want to return true. See Column H for formulas. H2 is: "=AND(NOT(ISBLANK($C2)),SUMPRODUCT(--(NOT(ISERR(SEARCH(CONCATENATE($F$2:$F$7,$C2,$G$2:$G$6),$D2)))))>0,SUMPRODUCT(--(NOT(ISERR(SEARCH($J$2:$J$5,$D2)))))=0)"

I added spaces to the words to provide proper spacing with concatenation.

Help Needed - Sumproduct search multiple concatnated strings in array.xlsx
	B	C	D	E	F	G	H	I	J
1	User Data Sample				Actual Customer Name between these data1	Actual Customer Name between these data2	Formula		And does not contain
2	WO	CUSTOMER	PARTS STATUS / JOB COMPLETE		from	inventory	FALSE		PARTS STATUS / JOB COMPLETE
3	101842	RandCO	From RandCo inventory		Parts are at	stock	TRUE		RM/WH
4	101843	RandCO	N/A		Parts at	WH	TRUE		02/WH
5	101844	RandCO	RandoCo has parts GB/WH		Use	Warehouse	FALSE		GB/WH
6	101845	Acompany	Parts are in Acompany Warehouse		Using	Parts	TRUE
7	101846	RandCO	Left Warehouse 02/27/2020		Parts are in		TRUE
8	101847	RandCO	Completed 04/22/2020		^^ Note - I want Sumproduct to search $D for matched string from Concat F2:F7&C2&G2:G6^^===>>		TRUE
Sumproduct Search

Cell Formulas
Range	Formula
H2:H8	H2	=AND(NOT(ISBLANK($C2)),SUMPRODUCT(--(NOT(ISERR(SEARCH(CONCATENATE($F$2:$F$7,$C2,$G$2:$G$6),$D2)))))>0,SUMPRODUCT(--(NOT(ISERR(SEARCH($J$2:$J$5,$D2)))))=0)

In case I am being confusing. Goal is:
Search $D for string match from "any string in $F2:$F7, concated with $C2, concated with any string in $G2:$G6.

 

[yky]

"SUMPRODUCT(--(NOT(ISERR(SEARCH(****CONCATINATE(------LIST1------$J$4:$J$120------LIST2****..........."

This should give you an error because CONCATENATE is misspelled and CONCATENATE doesn't take range as argument.

 

[JJenkx]

I have managed to get a working formula but It is longer than I hoped for. Does anyone have any suggestions?

On google docs here

Help Needed - 2ND From Work Sumproduct search multiple concatnated strings in array.xlsx
	A	B	C	D	E
1	User Data Sample			Actual Customer Name between these data1	Actual Customer Name between these data2
2	WO	CUSTOMER	PARTS STATUS / JOB COMPLETE	from	inventory
3	101842	RandCO	From RandCo inventory	Parts are at	stock
4	101843	RandCO	N/A	Parts at	WH
5	101844	RandCO	RandoCo has parts GB/WH	Use	Warehouse
6	101845	Acompany	Parts are in Acompany Warehouse	Using	Parts
7	101846	RandCO	From RandCos' inventory	Parts are in
8	101847	RandCO	Completed 04/22/2020
9
10		New formula works but is so long ========>>	FALSE	Almost settled for this ===========>>	FALSE
11		Is there a way to coerce Index into	FALSE	but It is not the perfect word logic I want	FALSE
12		returning all of the concatinated string sets from	TRUE		TRUE
13		the 5 OR formulas at one time? ======>>>	FALSE		FALSE
14	#VALUE!	<<<===== When evaluating excel sees all of the	FALSE		FALSE
15	#VALUE!	strings I am trying to return to the search formula	TRUE		TRUE
16	1	but I can't figure out how to return more than 6	FALSE		FALSE
17	#VALUE!	I am invested 24 hours into this one formula so far	FALSE		FALSE
18
Sumproduct Search

Cell Formulas
Range	Formula
A14:A17	A14	=SEARCH(INDEX($D$2:D$7&INDEX($B1&$E$2:E$6,N(IF(1,{1,2,3,4,5}))),N(IF(1,{1,2,3,4,5,6}))),$C1)
C10:C17	C10	=OR(ISNUMBER(SEARCH(INDEX($D$2:D$7&INDEX($B1&$E$2:E$6,N(IF(1,{1,2,3,4,5}))),N(IF(1,{1,2,3,4,5,6}))),$C1)),ISNUMBER(SEARCH(INDEX($D$2:D$7&INDEX($B1&$E$2:E$6,N(IF(1,{2,3,4,5}))),N(IF(1,{1,2,3,4,5,6}))),$C1)),ISNUMBER(SEARCH(INDEX($D$2:D$7&INDEX($B1&$E$2:E$6,N(IF(1,{3,4,5}))),N(IF(1,{1,2,3,4,5,6}))),$C1)),ISNUMBER(SEARCH(INDEX($D$2:D$7&INDEX($B1&$E$2:E$6,N(IF(1,{4,5}))),N(IF(1,{1,2,3,4,5,6}))),$C1)),ISNUMBER(SEARCH(INDEX($D$2:D$7&INDEX($B1&$E$2:E$6,N(IF(1,{5}))),N(IF(1,{1,2,3,4,5,6}))),$C1)))
E10	E10	=AND(SUMPRODUCT(--(NOT(ISERR(SEARCH($D$2:D$7&$B1,$C1)))))>0,SUMPRODUCT(--(NOT(ISERR(SEARCH($B1&$E$2:E$6,$C1)))))>0)
E11:E17	E11	=AND(SUMPRODUCT(--(NOT(ISERR(SEARCH($D$2:D$7&$B2,$C2)))))>0,SUMPRODUCT(--(NOT(ISERR(SEARCH($B2&$E$2:E$6,$C2)))))>0)
Press CTRL+SHIFT+ENTER to enter array formulas.