Using Data Validation Lists and the VLookUp Function in Excel

 

Assignment: Create an order form for Ajax Steak and Eggs restaurant.

Office 2007 instructions are in Red. Instructions specific to older versions are in Blue.

 

Ajax Steak and Eggs  -  Order Form

Item

Type

Preparation

Price

Steaks

Validation List

Validation List

VLookup

Eggs

W/Hash Browns & Toast

Validation List

$3.95

Omelet

Validation List

 

VLookup

Beverage

Validation List

 

VLookup

 

 

Total

=SUM

 

Create:

  1. A merged cell with the name of the form. See row 1 in the example.
  2. Some cells with labels. See the example.
  3. Three VLOOKUP tables for steaks, omelets and drinks. The data for the tables is given below. Build the VLOOKUP tables below the Order Form.
  4. Three Validation lists for the type of steak, type of omelet, and type of drink. These go in the second column of the form.
  5. Two Validation lists for the preparation options for eggs and for steaks.

These go in the third column of the form.

  1. Three VLOOKUP formulas showing the price of the selected food items.

These go in the fourth column of the form.

  1. A SUM function to total the bill. This goes in the bottom right corner of the form.
  2. Format the final column as Currency.
  3. Add some color to the table.
  4. Sort the tables so the first column of each table is in alphabetical order.
  5. Put your name somewhere in the worksheet.

 

To Write a VLOOKUP Formula:   Here’s a sample =VLOOKUP(C4,E6:H8,2) The first part (each part is called an argument) tells Excel to check in cell C4 to find the word to look for in the lookup table. The second argument tells Excel the location of the lookup table. This is a range (a rectangular shape) with opposite corners in cells E6 and H8. Notice that you don’t include the headings row (row 5) in the table location. 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 table.

 

 

Column E

Column F

Column G

Column H

Row 5

Data - Name

First Data Type – Age

Second Data Type - Weight

Third Data Type - Gender

Row 6

Al

22

175

Male

Row 7

Bob

27

189

Male

Row 8

Chris

18

154

Female

 

How it Works: Excel hunts in the first column of the lookup table for the word it found in cell C4. Suppose it was Bob.  When it locates Bob in the first column of the table, it knows the information you want Excel to display will be found in the Bob row of the table. Then it uses the third argument to find which column in the table (in this case, 2, or the second column) has the information that you want Excel to display.  In this case, the cell with the =VLOOKUP function would show the number 27.

 

To create a Validation List    Open the dialog box. From the Data Menu, Validation Submenu. Office 2007 – Look in the Data Tab, Data Tools Group. Data Validation is the icon in the upper right. 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.

 

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.

 

Information needed for the Lookup Tables and Validation Lists:

 

Customers can order:

Steaks:

Preparation Options: Rare, Medium, Well-Done

            New York                                                         $17.50

            Rib Eye                                                            $16.50

            Sirloin                                                              $13.95

 

Eggs, Hash Browns, and Toast:                                  $3.95

            Preparation Options: Scrambled, Over Easy, Sunny Side Up 

 

Omelets:

            Cheese:                                                           $4.45

            Ham:                Ham Cheese                            $4.95

            Cordon Bleu:   Chicken Ham Swiss-Cheese    $5.75

            Western:         Peppers Onion Ham Cheese   $5.45

 

Beverages:

            Juice:                                                               $1.19

            Coffee:                                                            $ .99

                        Milk:                                                                $ .99

 

Using Range Names:   You can assign a name to a range of cells and  use the name of the range in a formula. Suppose you defined the range E6:H8 as “people”. Then the function =VLOOKUP(C4,E6:H8,2) could also be written as =VLOOKUP(C4,people,2).  This is handy especially when the "list" or "table" is from a different sheet (and not the current sheet.) To create a name, look on the Insert menu, find Names and choose Define. Office 2007 -  Look in the Formulas Tab, Defined Names Group, Name Manager Icon to find tools to create, edit and delete range names.  You can also highlight the cells in the range and then click in the name box. Type in the name of the range. This works in both versions.

 

Eliminate the N/A error message by altering the formula to check for blank entries.

You can use a formula like this.   

 

=IF(COUNTIF(J6,""),"Retry", VLOOKUP(J6,A28:F31,6))

 

In this example J6 is the cell named in the first argument of the VLOOKUP function.

The first argument tests to see if the input cell (J6 in this case) is blank.

If J6 is blank, Excel displays the content of the second argument.

If J6 is not blank, Excel displays the result of the VLOOKUP function given in the third argument.