Question regarding Sumproduct Formula

[bluefish44]

Hi All and Thanks in Advance for your help.

I have the following formula which is working as expected...It gives a TRUE or FALSE as to whether a string in cell b6 is contained in a list of cells (J2:J26).
Instead of the TRUE/FALSE, I am hoping for the formula to return the Actual value in cells J2:J26 that it found in B6 causing the result to be true.

SUMPRODUCT(--ISNUMBER(SEARCH($J$2:$J$26,B6)))>0

 

[Eric W]

Try:

=LOOKUP(2,1/(SEARCH($J$2:$J$26,B6)*($J$2:$J$26<>"")),$J$2:$J$26)

 

[steve the fish]

This would find the first:

=INDEX(J2:J26,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(B6,J2:J26)),0),0))

This the last:

=LOOKUP(2,1/ISNUMBER(SEARCH(B6,J2:J26)),J2:J26)

If its just text you could use wildcard:

=INDEX(J2:J26,MATCH("*"&B6&"*",J2:J26,0))

 

[bluefish44]

that works! I'll have to study that one to understand but thanks for your help Eric

 

[bluefish44]

Steve the Fish - I tried your last formula as its really just the text 'm looking for but it didn't seem to work

 

[steve the fish]

Steve the Fish - I tried your last formula as its really just the text 'm looking for but it didn't seem to work

Really? Whats in B6? It should find whatever is in B6 within any part of any cell within J2:J26 and return that cells value.

 

[bluefish44]

see below - formula is in the far right column. The second one down should have returned S19MN because it is in the middle column but is giving an "N/A	CODES
MUG19CP	S19MN	MUG19CP
JEFELDMA,S19MN	BZ19vFs27	#N/A
	CD19cPx77	S19MN
40jwofyc,CALEE,S19MN	CMN19CP	#N/A
S19MN	MUG19CP	S19MN
S19MN	FF19CP	S19MN
S19MN	19MNtfC40	S19MN
S19MN	M19ESD	S19MN
S19MN	M19FMR	S19MN
S19MN	PETHOMPS	S19MN
CD19cPx77	EE19MK	CD19cPx77
CD19cPx77,S19MN	M19SCLC	#N/A
S19NPT	M19MLG	#N/A
	M19ESE	S19MN
	BUBIC	S19MN
S19MN	ANGIBSON
S19MN	MABRY
S19MN	GROWT
S19MN	OSTROFF
x3wfgnt7	SATAYLOR
S19MN,x3wfgnt7	GROVE
x3wfgnt7	KORN
BZ19vFs27	HKILBY
BZ19vFs27	M19SECP
S19MN	LANDSTRO

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

I think you've got it backwards, steve the fish: the OP is looking for the which value in J2:J26 can be found within B6.

bluefish44, try using the Evaluate Formula tool to see how it works. If you still have questions, let us know. That formula uses some poorly documented tricks!