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!!
=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!!
