If - then statements

[Jolene0322]

I have a spreadsheet with one column that indicates what type of Ultrasound we performed (i.e. OB, MFM, GYN) - is there a way to put a formula in so that if F1 = OB then J1 will = 35.00 (for all 3) - i.e. if J1 = MFM then J1 will = 65.00... not sure how to fun the formula for multiple values in F1. Thanks so much for the help!!!

 

[randomizer]

If you start needing more than three options then I'd try using a VLOOKUP instead. It's a bit more difficult to understand initially but once you start getting too many possibilities you'll find it much simpler to reason about than nested IF functions 8 levels deep. For three the IF is probably fine.

Here is a simple example of what you could do (although I'd put the lookup table on a separate sheet if you will need to reference it from more than one sheet).

I have the formulas showing so you can see what is going on. I've assumed that the number is cost for this example. VLOOKUP takes a couple of arguments.

■ Reference to the cell with a value that you are looking for. Here it is B2, B3 etc.
■ Reference to a range of cells that form the lookup table. The value in 1. is always looked up in the first column of this table regardless of how many columns it has. Here it is an absolute reference to all of columns E and F.
■ The column whose value you will retrieve if your lookup finds a match. Here I am looking up column 2. I could look up column 1 but that would give me my lookup value which isn't useful in this instance.
■ Whether to look up an approximate match or an exact match. Usually you want this to be exact (FALSE). In fact I've never come across a case where I didn't, so I don't even know what is considered "approximate".

Here is what the results will be.

I deliberately misspelled the last item to demonstrate a failed lookup. It's pretty obvious if you make a mistake in your VLOOKUP.

 

[Someone Somewhere]

I'm assuming that you want to put the formula in J1, and it's reading F1 (your second mention of J1 looks like it should be F1).

Try this:

IF(F1="OB", 35, IF(F1="MFM", 65, <whatever your price for GYN is>))

I'd suggest replacing the hardcoded values (35, 65 etc) with links to particular cells, so you can update it easier. You'd want absolute references (e.g. $Z$3) so that it doesn't change which cell it's looking at as you move down the column.

 

[randomizer]

If you start needing more than three options then I'd try using a VLOOKUP instead. It's a bit more difficult to understand initially but once you start getting too many possibilities you'll find it much simpler to reason about than nested IF functions 8 levels deep. For three the IF is probably fine.

Here is a simple example of what you could do (although I'd put the lookup table on a separate sheet if you will need to reference it from more than one sheet).

I have the formulas showing so you can see what is going on. I've assumed that the number is cost for this example. VLOOKUP takes a couple of arguments.

■ Reference to the cell with a value that you are looking for. Here it is B2, B3 etc.
■ Reference to a range of cells that form the lookup table. The value in 1. is always looked up in the first column of this table regardless of how many columns it has. Here it is an absolute reference to all of columns E and F.
■ The column whose value you will retrieve if your lookup finds a match. Here I am looking up column 2. I could look up column 1 but that would give me my lookup value which isn't useful in this instance.
■ Whether to look up an approximate match or an exact match. Usually you want this to be exact (FALSE). In fact I've never come across a case where I didn't, so I don't even know what is considered "approximate".

Here is what the results will be.

I deliberately misspelled the last item to demonstrate a failed lookup. It's pretty obvious if you make a mistake in your VLOOKUP.