Using IF with multiple non numerical values??

[Mad Ad]

My eyes are going crossed trying to read history here, and I cant search for IF so Ill have to just come out and ask my question.


=IF(G3="XL";20;0) Works fine, put XL in the box, out pops 20, otherwise 0, but what if I have a range in that one cell? Such as M L XL? each with a different then value and no otherwise value?

How do i format this please?

Thanks a lot

[squenson]

You could use the function FIND to find a string within another string But be careful, as finding the string "L" in "M XL" will return a positive find, so include an initial space character in your search string, and one at the beginning of the reference string. Something like:
=IF(ISERR(FIND(" L";" M XL";1));"Unknown size";"Size L")

[keme]

IF() is meant for a simple selection between two options. You can nest one IF() inside another for more complex selections, but there are better tools for that (if I'm reading you right here). I suggest you make it in a couple of steps:

First make a list of permissible values
Create a new sheet or rename a blank sheet to "Sizes" (I'm guessing that you want to use clothing sizes. Right click on the tab and select the insert or rename option.)
On that sheet, enter the sizes down column A:
XS, S, M, L, XL, XXL
In column B, enter the value to be returned for each selection.

Make your conversion from size to number
In another sheet, designate a cell to enter a value into.
Use the MATCH() function to determine the position of the item in the list.
Use the INDEX() function to pick the correct return value from the other list.

You can also use VLOOKUP() to return the value directly, or use CHOOSE() and enter the value list directly in the formula.
All the options mentioned are used in the attached file, which also uses data validation to ensure you enter a valid size.