Nested IFs AND/OR Dilemma

I have a spreadsheet with a lot of different options for text and I need to analyze a cell and return a numeric value. I've tried nested IFs but that doesn't seem to work with text so I'm kind of stuck. Any help is greatly appreciated.

EXAMPLE:

Cell A1 could contain "None", "Non-sterile", "EO", "R", or "Temp"

I want to return a number into a different cell depending on what is in the first cell i.e. if "None" enter 0, if "Non-sterile" enter 1, if "EO" enter 2, if "R" enter 3, or if "Temp" enter 4.

This particular example has the most options. I have a lot of cells that are a simple IF X than 0, IF Y then 1 and those equations are working just fine. I just can't figure out how to return a value dependent on what text is in the cell. Do I need to make a table with these options and do a LOOKUP instead? If so, what's the best way to go about doing that as I've never used the LOOKUP function, I only know it exists.

TIA!
Pooch

Thanks for the help everyone! You are Excel saviors! I would have tried to post an example but since I wasn't even sure which route to take to get the desired outcome it wouldn't have helped much.

I did originally try that nested IFs equation and had it written just like that but for some reason it wasn't giving me the right values. The LOOKUP(SUBSTITUTE solved this problem though. Thanks again everyone, you're all great!

I prefer VLOOKUP for this kind of thing as then typos return errors rather than matching the wrong answer, e.g.

=VLOOKUP(A1,{"None",0;"Non-sterile",1;"EO",2;"R",3;"Temp",4},2,0)

I find it easier to input this way as well as it doesn't need to be in a particular order and the lookup value is entered next to the key. Though personally I would have the lookup range someone in the workbook in 99% of cases.

Originally Posted by nick.williams

I prefer VLOOKUP for this kind of thing as then typos return errors rather than matching the wrong answer, e.g.

=VLOOKUP(A1,{"None",0;"Non-sterile",1;"EO",2;"R",3;"Temp",4},2,0)

I find it easier to input this way as well as it doesn't need to be in a particular order and the lookup value is entered next to the key. Though personally I would have the lookup range someone in the workbook in 99% of cases.

What are the 2 & 0 referring to at the end of that equation?

VLOOKUP looks up a value in the left-most column of an array of data, and returns the value in the specified column. The 2 refers to the 2nd column.

The 0 is what I am in the habit of using instead of FALSE (Excel treats them the same and I type it a lot). This means it searches for an exact match, rather than an approximate match if 1 or TRUE is used.

Hope that helps,

Nick