Confirm matching text in cell and then return a numerical value from a table

[lrntolive]

Hello all and thank you for your help.

I've run into an issue in Excel, and I cannot seem to get any formulas to work. This may be beyond the capabilities of Excel, but here goes.

I need to perform the following functions and return a single value:
1) Search a text string in a single cell to confirm certain text is contained in the string
2) If said text is found, return a numerical value from a table with matching text string

Here is the example text string (all contained in one cell):
[TABLE="width: 420"]
<tbody>[TR]
[TD="width: 420, colspan: 5"]CAT18-EC-FW-FGP, LW, SDE, NOF, FG, RHS, PLBR[/TD]
[/TR]
</tbody>[/TABLE]

I would like to confirm whether the text string has an "FW" or "SW" within the text string. The text string will not always be in the same spot.
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]String[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]FW[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]SW[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Also, I will need to be able to search other text strings with other values, but I thought this would be enough to get started.

Excel Version 14.0 32-bit version

Thank you

 

[Fazza]

what about
=SUMIF(CellWithString,"*"&LookupString1&"*",ReturnValue1)+SUMIF(CellWithString,"*"&LookupString2&"*",ReturnValue2)

 

[lrntolive]

I can't seem to figure out if I'm doing something wrong, but the formula returns the same value whether or not I change the text string.

For example: I changed the "FW" in "CAT18-EC-FW-FGP, LW, SDE, NOF, FG, RHS, PLBR" to "SW", but the formula still output a "0". "SW" should return a value of "1".

Here is what I did: =SUMIF(B3,"*FW*",A1:B2)+SUMIF(B3,"*SW*",A1:B2)

I used the same information as above to test.

I tried this, and it worked, but I'd rather have a function that is not hard coded with the values in it to prevent errors:
[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]=SUMPRODUCT(--ISNUMBER(SEARCH({"*FW*";"*SW*"},B3)),A1:B2)

Any ideas? thank you.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrntolive]

After further testing using the SUMPRODUCT function, I found it has a problem with strings that are similar and ends up adding values together.

Example: "HW" and "EHW"

This solution didn't work out like I hoped. Any other ideas?

Thank you

 

[Fazza]

sorry that the formula offered wasn't clear: i didn't know what cells to reference

instead of the used version =SUMIF(B3,"*FW*",A1:B2)+SUMIF(B3,"*SW*",A1:B2)

try the intended =SUMIF(B3,"*"&A1&"*",B1)+SUMIF(B3,"*"&A2&"*",B2)

if the requirement is slightly different now, it needs to be explicitly described.
Please clearly explained it with sample data (input & corresponding output) that can be copied to Excel