Excel Data Functions                                                   © Mike Splane 2007

 

IF Functions

IF Functions are based on computer logic. They have three parts, called arguments:

IF                    something is true

THEN              do this

ELSE               do something else

 

In Excel, an If function displays consists of a test (the IF),

what to display if the test is passed (the THEN),

and what to display if the test is not passed (the ELSE).

 

IF

If C3 is > 80

THEN

Display “Pass”

ELSE

Display “Fail”

 

Suppose your score is recorded in cell C3 as a number. An IF function could be written as =IF(C3>80, “Pass”,”Fail”) The cell containing the IF function would show Pass or Fail.

 

  • The first part, (each part is called an argument) tells Excel to check in cell C3, use that value in the equation, and then determine the result of the equation.
  • The second argument tells Excel what to display if the condition is met, in this case if the value is greater than 80, then the condition is met. The word PASS will appear in cell D3.
  • The third argument tells Excel what to display if the condition is not met, in this case if the value is NOT greater than 80 then the condition is not met.  The word FAIL will appear in cell D3.

 

The second and third arguments in IF Functions can be

  • Text (called strings), always written between double quotes, or
  • Numbers, or
  • Formulas, or
  • Functions.
  • You can even use an IF Function as an argument. This is called a nested IF.

 

VLOOKUP Functions

 

If you have a table of several items to choose from, you would use a VLOOKUP function to find a specific piece of data. For example, you could have a table showing the sales tax rates for different counties. To find the tax rate, Excel will need to where the table is stored, which row to look in, and which column to look in.

 

How it Works: You provide Excel with a key word. Excel finds the key word in the table and selects that row of data. You provide Excel with the column number to look in. Excel finds the column and displays its contents in the cell containing the VLOOKUP function.

 

Here’s a sample =VLOOKUP(C4, G5:H7 ,2) The first argument (each part is called an argument) gives Excel the location of the word it will look for in the table. Excel will search the table for this word, and select that row as the data row.  The second argument tells Excel the location of the lookup table. This is a range (a rectangular shape) with opposite corners in cells G5 and H7. The third argument, which is always a number, tells Excel that the information is found in the second (or third, or fourth, or fifth, and so on) column of the selected row.

 

To create a VLOOKUP Table:  The first column will contain the name of the item to be looked up. List these items in alphabetical order. Additional columns will contain information about the items in the first column.

 

EXAMPLE:

Name

Major

Class

Hobby

Chris

Finance

Junior

Shopping

Drew

Marketing

Sophomore

Video Games

Kim

MIS

Senior

Dancing

 

Range Names 

 

You can assign a name to a group of cells by highlighting the cells with the mouse, then typing the name in the name box above cell A1. You can view range names by clicking the pull-down arrow in this box.

 

If the table has a name assigned to it you can use the name in the formula instead of the cell notation. Suppose you were to assign the name Students to the cells in the table above. You could rewrite the VLOOKUP formula as =VLOOKUP(C4,Students,2).Note: Range names do not have quotation marks around them when you use them in a formula.

 

Data Validation

 

If a user enters a word into the Lookup cell that is not found in the table, the VLOOKUP Function will not be able to locate the word and will generate false results. To prevent this problem a restriction is usually placed on the words that can be entered into the Lookup cell. This restriction is called a data validation list. It will contain only the words found in the first column of the VLOOKUP table.

 

To create a Validation List    From the Data Menu, Validation Submenu, Settings tab, Validation Criteria, Allow box, choose List. In the Source Box, you can either type in a list of options, separated by commas, or click in the box, and then use the mouse to highlight the cells that contain your list.