Question regarding Sumproduct Formula

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
185
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
 

Some videos you may like

Excel Facts

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Try:

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 3, 2009
Messages
185
that works! I'll have to study that one to understand but thanks for your help Eric
 

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
185

ADVERTISEMENT

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
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 3, 2009
Messages
185
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
MUG19CPS19MNMUG19CP
JEFELDMA,S19MNBZ19vFs27#N/A
CD19cPx77S19MN
40jwofyc,CALEE,S19MNCMN19CP#N/A
S19MNMUG19CPS19MN
S19MNFF19CPS19MN
S19MN19MNtfC40S19MN
S19MNM19ESDS19MN
S19MNM19FMRS19MN
S19MNPETHOMPSS19MN
CD19cPx77EE19MKCD19cPx77
CD19cPx77,S19MNM19SCLC#N/A
S19NPTM19MLG#N/A
M19ESES19MN
BUBICS19MN
S19MNANGIBSON
S19MNMABRY
S19MNGROWT
S19MNOSTROFF
x3wfgnt7SATAYLOR
S19MN,x3wfgnt7GROVE
x3wfgnt7KORN
BZ19vFs27HKILBY
BZ19vFs27M19SECP
S19MNLANDSTRO

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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! :eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,360
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top