The Excel IF function is like a nice pair of black shoes: they’re fundamental and fly under the radar. When asked about a list of Top Excel Formulas, I immediately thought of the almighty VLOOKUP. But Wait! My #1 is IF and #2 is VLOOKUP.

IF is fundamental like black shoes.

#1 Function: Excel IF function

Why the IF Function as #1 before VLOOKUP?

IF statements show up everywhere! Let’s think in English and our regular lives:

  • If there is a parking spot out front, I’ll go move my car from the down the block.
  • If there is no more milk, I’ll go buy milk AND pick up my dry-cleaning.
  • If Toni visits AND it’s before 6PM, I’ll ask her to translate a Portuguese document that I have; otherwise, we have to go straight to dinner.
  • If Q3 AND Q4 revenue don’t improve for product XYZ, we will drop the product AND donate remaining items to local charities.
A VLOOKUP statement would be:
  • Look at this bag of flour in the kitchen, go to Whole Foods in the baking section, bring me back the price of this flour, if you don’t find exactly this bag of flour don’t bring me any other prices.

If the IF function is like a nice pair of black shoes, the VLOOKUP would be sweet pair of cowboy boots. A person rockin’ cowboy boots knows what they’re doing and has a brashness about them that isn’t available to someone who just owns black shoes. But cowboy boots aren’t as versatile as black shoes. So, let’s go with versatility before we go bold. Bold without versatility will get you in a jam really fast. Ergo, I choose IF as #1 before VLOOKUP.

Excel IF Function Syntax

=IF(condition, if true, if not true)

The syntax
3 parts separated by commas

I think of it this way

  1. Condition
  2. What to do if the condition is true
  3. What to do if the condition is not true
  1. What do you want me to look for?
  2. If I find it, then what?
  3. Otherwise ….?

=IF(there is a parking spot out front, move my car, don’t do anything)

=IF(AND(Toni visits, it’s before 6pm), ask her to translate the document, go straight to dinner)

Let’s do a simple IF for finding duplicates

=IF(X is the same as Y, create an alert, don’t do anything

In this video we start with a list of 125 names and associated states. We do 2 things:

  1. Determine how many Names are duplicates
    • Do we have more than 1 Michael?
  2. Determine how many Names AND their associated States are duplicate
    • In this case, it’s not good enough to find 2 Michaels.
    • We want to match them only if, for example: we get 2 Michaels that are both in Michigan.
    • We don’t care if one Michael is in Michigan and the other Michael is in Florida.

Have a look! Let me know if this helps or if you have questions.

        

What are some uses of the IF function that you can think of?

photo credit: beepola via photopin cc