lookup partial strings

[Tigerexcel]

Am trying to get the spreadsheet to return the number of times a partial string is contained in a word depending on which list the substrings come from. The desired output is in Column C. The first record Seinfeld contains ei and thus returns 1 only. I imagine this might require an Array function but any Excel function deemed appropriate will do.

Word	List	Answer		List A	ei	ot	an	sk	gh
Seinfeld	List A	1		List B	er	ee	ef	ow	ev
Fred Evans	List B	1		List C	op	ck	at	ei	it
Mary Ansky	List A	2
Cate	List C	1
Acer	List B	1
Show tee	List B	2
Janet	List A	1
Carrot	List A	1
Opat	List C	2
Skylight	List A	2

 

[ngoctinh87]

Am trying to get the spreadsheet to return the number of times a partial string is contained in a word depending on which list the substrings come from. The desired output is in Column C. The first record Seinfeld contains ei and thus returns 1 only. I imagine this might require an Array function but any Excel function deemed appropriate will do.

Word	List	Answer		List A	ei	ot	an	sk	gh
Seinfeld	List A	1		List B	er	ee	ef	ow	ev
Fred Evans	List B	1		List C	op	ck	at	ei	it
Mary Ansky	List A	2
Cate	List C	1
Acer	List B	1
Show tee	List B	2
Janet	List A	1
Carrot	List A	1
Opat	List C	2
Skylight	List A	2

Try

C2=SUMPRODUCT(--ISNUMBER(SEARCH(INDEX($F$1:$J$3,MATCH(B2,$E$1:$E$3,),),A2)))

 

[Tigerexcel]

Thanks Ngoc, it works. I'm having a little trouble understanding a part of it. I step through the formula with F9 and the INDEX($F$1:$J$3,MATCH(B2,$E$1:$E$3,),) part gives me an error #REF. Is this because it's trying to return an array.

I've got another question as well for anyone who wants to chime in, as I was waiting for a response to this thread, my attempt at producing a solution to this was:

=SUMPRODUCT(($G$1:$G$3=B2)*(ISNUMBER(SEARCH($H$1:$L$3,A2)))) which seems to work ok but I'm not sure how it adjusts for the different list option ie how does it adjust for List A compared to B unless the G1:G3=B2 creates a single row array that multiplies by another single row array. How does it remove the unwanted entries from H1:L3?

 

[Fluff]

When posting data to the board it's best to use the "Generate output" button, rather than the "Table only", that we we can see where your data is located.
I suspect that you get the error because your data does not start in A1.

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Word	List	Answer		List A	ei	ot	an	sk	gh
2	Seinfeld	List A	1		List B	er	ee	ef	ow	ev
3	Fred Evans	List B	1		List C	op	ck	at	ei	it
4	Mary Ansky	List A	2
5	Cate	List C	1
6	Acer	List B	1
7	Show tee	List B	2
8	Janet	List A	1
9	Carrot	List A	1
10	Opat	List C	2
11	Skylight	List A	2
Sheet2

Cell Formulas
Range	Formula
C2:C11	C2	=SUMPRODUCT(--ISNUMBER(SEARCH(INDEX($F$1:$J$3,MATCH(B2,$E$1:$E$3,),),A2)))

 

[Tigerexcel]

Thanks Fluff for the feedback and I'll use the Generate output option. So this leaves one more question:

=SUMPRODUCT(($G$1:$G$3=B2)*(ISNUMBER(SEARCH($H$1:$L$3,A2)))) which seems to work ok but I'm not sure how it adjusts for the different list option ie how does it adjust for List A compared to B unless the G1:G3=B2 creates a single row array that multiplies by another single row array. How does it remove the unwanted entries from H1:L3?

At this stage the above formula seems to work and indicates that you can achieve results with different formulae, just got to get my head around these array calculations.

 

[Fluff]

Best way to "see" what a formula is doing is to use the Evaluate formula button on the formula tab.