Sum of Returned Lookup Values in a Text Cell [Closed]

Report
-
BaCkToHaCk
Posts
90
Registration date
Thursday July 30, 2015
Status
Member
Last seen
May 13, 2016
-
I'm using the lookup formula below to search user comments in cell B1 to find individual words listed in column C, and return the adjacent number value found in column D.

=LOOKUP(9999,SEARCH($C$1:$C$837,B1),$D$1:$D$837)

The problem I'm running into is that excel only returns the number value for the first word that it finds, when I really want it to return the sum of the adjacent number values of all of the words it finds in the cell.

Example: the comment: "The stores should stock more makes and more current styles and colors", should give me a comment sum of 24 - 10 for "styles" and 14 for "colors", but excel is only returning a 10 for "styles".

Please help!
Thank you!!

1 reply

Posts
90
Registration date
Thursday July 30, 2015
Status
Member
Last seen
May 13, 2016
26