# Question regarding Sumproduct Formula

#### bluefish44

##### Board Regular

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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Eric W

##### MrExcel MVP
Try:

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

#### steve the fish

##### Well-known Member
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

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

#### bluefish44

##### Board Regular

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

##### Well-known Member
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

##### Board Regular
 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

##### MrExcel MVP
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!

Replies
2
Views
94
Replies
2
Views
83
Replies
2
Views
73
Replies
7
Views
83
Replies
1
Views
43