multiple if's and case sensitivity

Scenario:
- Column A will either contain the text value "yes" or "no"
- Column B will contain a variety of text in each cell but the specific word I'm interested in amongst the variety of text in each cell will be in all CAPS. The three specific words in CAPS that I'll be looking for are "FEE", "FI", "FO".

How can I make it so in Column C it displays a value of "5" if "FEE" is in Column B and "yes" in Column A. But a value of "10" if "FI" is in Column B and "yes" in Column A. But a value of "15" if "FO" is in Column B and "yes" in Column A. BUT if "no" is ever in Column A then Column C will always have a value of 0 (zero).

Hi

See if this gets you there.

=IF(UPPER(A1)="NO",0,CHOOSE(SEARCH(1,--ISNUMBER(SEARCH("FEE",B1))&--ISNUMBER(SEARCH("FI",B1))&--ISNUMBER(SEARCH("FO",B1))),5,10,15))


rylo

but SEARCH isn't case-sensitive, you need to use FIND, try

=IF(A1="no",0,IF(A1="yes",LOOKUP(2^15,FIND({"FO","FI","FEE"},B1),{15,10,5}),""))

Note that this would return #N/A if A1 = yes but none of the criteria are found in B1. To avoid that and give a zero instead

=IF(A1="no",0,IF(A1="yes",LOOKUP(16,CHOOSE({1,2},0,LOOKUP(2^15,FIND({"FO","FI","FEE"},B1),{15,10,5}))),""))

Thanks, between the both of your replies I think I found something I can use. Question about rylo's response.. what does the 1 represent that I bolded below from your reply..

=IF(UPPER(A1)="NO",0,CHOOSE(SEARCH(1,--ISNUMBER(SEARCH("FEE",B1))&--ISNUMBER(SEARCH("FI",B1))&--ISNUMBER(SEARCH("FO",B1))),5,10,15))

Hi

Apart from confusing the FIND and SEARCH case sensitivity, the section
--ISNUMBER(SEARCH("FEE",B1))&--ISNUMBER(SEARCH("FI",B1))&--ISNUMBER(SEARCH("FO",B1))
produces a concatenated result of 1 and 0 (eg 100, 010...). The search of 1 finds the position in the string of the valid result. This is then used in the CHOOSE function to select the relevant return value.


rylo