Excel Lookup Functions

This lesson covers methods of pulling information out of a table.

Instructions:

Read through the set of learning objectives. This is what you should know when you finish the lesson.

Watch the YouTube videos before starting the lesson.

Complete every step in the assignment, in the order given. Do not skip steps.

When you have done all of the steps, fill out the Check for Understanding (CFU) form.

A completed CFU for every lesson, in-class and homework, is due to be turned in during your next class. 

The CFU forms are not to be submitted by email.

Where there are differences between software versions, the instructions for Excel 2010 are in Black, for the Mac are in green and for Excel 2013 are in blue.

Summary of Key Points:

Create a LOOKUP Function.

Create a data validation list.

Understand what causes the #NA error and how to fix it.

Create a VLOOKUP Function.

Create an INDEX function

Create a MATCH function

Videos:

Use the VLOOKUP Function     Time = 1:03

https://www.youtube.com/watch?v=YVemT_-k1zk

Data Validation     Time = 1:11

https://www.youtube.com/watch?v=osqLK7mB8VU

VLOOKUP Function

Review the skills covered in this lesson     Time = 6:37

https://www.youtube.com/watch?v=iasekZJrCnU

 

Lookup Functions for the MAC:   https://www.youtube.com/watch?v=KAnfNod8nTY

Vlookup for the MAC  https://www.youtube.com/watch?v=J1xPOekfmY8

About Lookups

Look at this table.

In computer programming terminology this is a table describing an Object Class called Students.

The individual students in the Students column are called Instances.

Students share several characteristics, called Attributes. These are listed in columns 2, 3, 4 etc.

 

Students

Age

Major

GPA

City

Beverly

22

Management

3.61

Santa Clara

Jon

20

Environmental Studies

3.72

Santa Cruz

Sue

18

Pre-Law

3.43

San Jose

 

There are three kinds of Lookup functions that can be used to find information from an Object table. The choice of which one to use is based on how the data in the table is organized. 

 

LOOKUP is used when your data table only contains two columns, like this Menu Items table.  In this type of table the objects only share one attribute. Here it is Price.

Menu Items

Price

MegaMeal

$6.00

Big Burger

$3.00

Fries

$2.00

Soft Drink

$2.00

 

When the data table has more than two columns, (two or more attributes)  VLOOKUP is used. For students I could have columns for names, majors, class, age, GPA, gender, and so on. VLOOKUP is used when the instances and attribute information are stored in columns (V =Vertically ) 

Students

Age

Major

GPA

City

Student ID

Beverly

22

Management

3.61

Santa Clara

10483824

Jon

20

Environmental Studies

3.72

Santa Cruz

10493269

Sue

18

Pre-Law

3.43

San Jose

10297079

 

An Object table can also be laid out with the instances listed in the top row and the attributes laid out in columns. HLOOKUP is used when the instances and attribute information is stored in rows (H =Horizontally).

 

Menu Items

Big Burger

Fries

MegaMeal

Soft Drink

Price

$3.00

$2.00

$6.00

$2.00

Description

Giant burger with all the trimmings.

Medium  Size Curlicue Fries

Big Burger, Fries, and a Soft Drink.

Medium Size Soda with Ice

 

 

Steps

First open Excel. Download this Excel file and save it on the desktop. http://bus91L.altervista.org/Data/2013Data.xlsx

1.       Go back into Excel and then open the file. Switch into the Tables worksheet.

Examine the LOOKUP function in cell H4 in the Tables worksheet: =LOOKUP(F4,A5:B13). It has two arguments. Cell F4 will contain the name of a specific item (the instance) from the menu group that you want to learn about. The range A5:B13 is the location of the Menu table. When the name of an instance is entered into cell F4 the LOOKUP function will find that item in the Menu table and then display its attribute (the price) in cell H4.

Menu Items Table

Item

Price

Beer

$3.00

Coffee

$1.75

Juice

$1.50

Salad

$4.25

Sandwich

$4.95

Soda

$1.50

Soup

$2.95

Tea

$1.50

Wine

$4.00

 

  1. Change the text in cell F4 of the Tables worksheet to replace the word Beer with the word Coffee. Notice how the price changes in cell H4.

Data Validation

What happens when you enter a search-term (a word) into cell F4 that doesn’t match any of the instances? You get a random result. Programmers call this type of error GIGO. It stands for Garbage In = Garbage Out.

  1. Try ordering something that is not on the menu. Type Pie into cell F4 and press Enter. Even though Pie is not listed in the menu Cell H4 still shows a price.

 To avoid GIGO errors we can use the data validation tool to block garbage entries. Let’s add one now.

  1. Click on cell F4. On the Data section of the ribbon click on the Data Validation icon [ALT  A  V]. In Excel 2013 use [ALT  A  V  V].

A dialog box opens. Select the Settings tab at the top of the dialog box. 

  1. For entries into the F4 cell we want to allow only the names of the instances listed in the menu table. Change the “allow” option to List.
  1. Click in the cell-selector icon in the “source” box. Click and drag over cells A5 to A13 to select them as the list source.  Press Enter.

Note that you can also type the list directly into the source box, using a comma to separate each item in the list. The mouse method is faster, prevents typos, and has one other advantage. If you change the text in any one of the selected cells, the validation list updates itself automatically.

Examine the other options in the Allow box. When do you think you would use one of these?

  1. Click on cell F4. On the Data section of the ribbon click on the Data Validation icon [ALT  A  V]. In Excel 2013 use [ALT  A  V  V].
  2. A dialog box opens. Select the “Error Alert” tab. In the main message box type: “This item is not on the menu.” and press OK.
  1. Now click on cell F4 and type in the word Coke.  Does the message appear?  Click on Cancel.
  1. Click in the input cell F4. Do you see a drop-down list? You can use the data validation tool to create drop-down lists for use in forms. Select sandwiches from the drop-down list.

You can copy data validation formats from one cell to another. First copy the cell with the data validation, and then select another cell or range of cells. Right-click and select Paste Special, and then choose Validation.

Data Validation is not foolproof. It fails if content is pasted into the cell instead of typed in.

 

VLOOKUP Function

If the data table has three or more columns we use a different function, called VLOOKUP. The V means the labels that are compared to the input search term are listed vertically.

YouTube video – VLOOKUP Function

Here is an example of a VLOOKUP Function =VLOOKUP(F19,A19:D24,4)

It is similar to the LOOKUP function but it has an extra argument. This argument refers to which column to look in for the desired attribute. Entering a 2 here refers to the attribute in the second column, a 3 refers to the attribute in the third column, and so on.

 

For the County Table we will put the name of a county into an input cell, and then use three VLOOKUP functions to display the Tax Rate, Delivery Fee, and Travel Time in three output cells:

=VLOOKUP(F19,A19:D24,2),  =VLOOKUP(F19,A19:D24,3), and  =VLOOKUP(F19,A19:D24,4)

County Data Table

County

Tax Rate

Delivery Fee

Travel Time

Contra Costa

8.25%

$10.00

40

Monterey

7.25%

$15.00

80

San Francisco

8.50%

$15.00

60

San Mateo

8.25%

$10.00

40

Santa Clara

8.25%

$5.00

20

Santa Cruz

8.00%

$10.00

50

 

  1. Change the name of the county in cell F19 to Monterey. The values in H19 H20 and H21 should change. Did they?

 

Presidents

Presidents

Took Office

Years

Noted For

Kennedy

1961

3

Space Race

Johnson

1963

5

Civil Rights

Nixon

1969

6

Watergate

Ford

1974

2

Appointed

Carter

1977

4

Inflation

Reagan

1981

8

Berlin Wall

Notice the VLOOKUP function in cell H29, =VLOOKUP(F29,A29:D34,4),  is returning the error message #N/A.  You will get that message if the input cell is empty or if the table is not in alphabetical order. Here our problem is the table is not in alphabetical order so we will sort the data.

 

  1. Mouse over the range A29:A34 (just the names of the Presidents) to select it. On the Home tab of the ribbon click on the A-Z icon [ALT H  S].  STOP!

 

You should see a message asking you if you want to expand the range to sort. If you choose continue with the current selection you will only sort the names in the first column of the table. That will ruin your data.  We should have selected the entire table, or clicked in just a single cell, before clicking on the A-Z icon.

  1. Click Cancel.
  1. Mouse over the range A29:D34 (all four columns) to select it. On the Home tab of the ribbon click on the A-Z icon [ALT  H  S  S]
  1. Did the #N/A message in cell H29 go away? It should show the word Appointed.
  1. Click on cell F29 and select another president. Did it work?

Project: AJAX Steak and Eggs

Let’s practice by creating an interactive form that a server can use when taking orders from customers.

AJAX Steak and Eggs

Item

Type

Preparation

Price

Description

Steak

validation list

validation list 

LOOKUP 

 

Eggs

With hash browns

validation list 

$3.95

 

Omelet

validation list 

 

VLOOKUP 

VLOOKUP

Side Order

validation list 

 

 LOOKUP

 

 

 

Total

$3.95

 

 

Steaks Table

Steaks

Price

New York

$17.50

Rib Eye

$16.50

Sirloin

$13.95

 

Side Orders Table

Side Order

Price

Bacon

$2.00

Fruit

$2.50

Ham

$1.75

Pancakes

$1.50

Sausage

$2.25

 

Omelets Table

Omelets

Price

Description

Cheese

$4.45

Cheese

Cordon Bleu

$5.75

Chicken Ham Swiss-Cheese

Ham

$4.95

Ham Cheese

Western

$5.45

Peppers Onion Ham Cheese

Start by creating validation lists for the Steak type, the Omelet type, and the Sides type. If you don’t remember how to do a validation list, review steps 4 to 8 in this assignment.

  1. Click on cell G42. Create a data validation list [ALT  A  V  V] using the cells F51:F53 as the source. Select a steak.

 

New York

Rib Eye

Sirloin

 

  1. Click on cell G44. Create a data validation list [ALT  A  V  V] using the cells F57:F60 as the list source. Select an omelet
  1. Click and drag over the range F64:F68. In the name box, type in the word Sides and press Enter. We have now created a range name, Sides, we can use as the source for our data validation list.

Click on cell G45. Create a data validation list [ALT  A  V  V]  using the word =Sides (without quotation marks. The equal sign is required.)  as the list source. Select a side order.

  1. Click on cell H42. Create a data validation list. [ALT  A  V  V]   Type” Medium,Rare,Well Done” (without the quotation marks) into the source box. Notice it is ok to include spaces, the commas separate the options. Select one of the options.
  1. Click on cell H43. Create a data validation list. [ALT  A  V  V]  Type” Scrambled, Sunny Side Up,Over Easy,Over Hard” (without the quotation marks) into the source box. Did you make any typing mistakes?  Select one of the options.
  1. Click on cell i42. Enter a LOOKUP function to find the price of the steak:  =LOOKUP(G42,F51:G53).
  1. Click on cell  i45. Enter a LOOKUP function to find the price of the Side Dish: =LOOKUP(G45,F64:G68).
  1. Click on cell  i44. Enter a VLOOKUP function to find the price of the Omelet: =VLOOKUP(G44,F57:H60,2) . Note that the 2 is needed to tell Excel that the value we want is found in the second column of the table.

Range Names

Now I want to create a range name for the Omelets table and use that in my VLOOKUP formula.

  1. Click and drag over the range F57:H60. Type the word Omelets in the name box and press Enter. The name box is just above the worksheet. It’s on the left.

Named Cell Ranges   1:29

Formula Bar and Name Box

If you can’t find the name box go to the Formulas tab and click on the Range Manager icon [ALT  M  N] and click on New in the Name Manager dialog box.  

For the Mac click on The Insert menu, find Name, and click on Define.

 

Omelets Table

Omelets

Price

Description

Cheese

$4.45

Cheese

Cordon Bleu

$5.75

Chicken Ham Swiss-Cheese

Ham

$4.95

Ham Cheese

Western

$5.45

Peppers Onion Ham Cheese

 

  1. Click and drag over the range F64:G68. Type the word SidesList in the name box and press Enter.
  1. In the name box, click on the pull-down arrow to see a list of named ranges.
  1. On the Formulas tab of the ribbon, click on the Name Manager icon [ALT  M  N] to see the range names. For the Mac click on the Insert menu, find Name, and click on Define. Click on SidesList, Click on Delete, click on Ok, and click on Close.
  1. Click on cell J44. Enter a VLOOKUP functions to find the description of the Omelet: =VLOOKUP(G44, Omelets, 3) .

Project:  Java Juice Delivery Form

  1. Create a data validation list [ALT  A  V  V] of county names in cell B47. Use the range A19:A24 as the list source.

 

Contra Costa

Monterey

San Francisco

San Mateo

Santa Clara

Santa Cruz

 

  1. Click in cell B47 and select San Mateo for the county.
  1. We can create data validation lists in several cells at the same time. Select cells B52 to B60. Create a data validation list using the range A5:A13 as the list source. 

Next we need to create two range names for the Menu and County tables. Validation lists only include the column that includes titles. For the range names we need to include all of the data columns as well as the labels column. 

  1. Click and drag over the range A5 to B13. This range is two columns wide, not just one.  Type the word Menu in the name box. Press Enter.

 

Range Name Menu

Item

Price

Beer

$3.00

Coffee

$1.75

Juice

$1.50

Salad

$4.25

Sandwich

$4.95

Soda

$1.50

Soup

$2.95

Tea

$1.50

Wine

$4.00

 

  1. Click and drag over the range A19:D24. This range is four columns wide, not just one. Type the word County in the name box. Press Enter.

 

County Data Table

County

Tax Rate

Delivery Fee

Travel Time

Contra Costa

8.25%

$10.00

40

Monterey

7.25%

$15.00

80

San Francisco

8.50%

$15.00

60

San Mateo

8.25%

$10.00

40

Santa Clara

8.25%

$5.00

20

Santa Cruz

8.00%

$10.00

50

 

  1. In cell C52, enter a LOOKUP Function to find menu prices. The input cell is B52. Use Menu, with no quotation marks, as the range argument. =LOOKUP(B52,Menu) Copy the function into cells C53 to C60. The cells in the C52:C60 range should show either #N/A or a number.
  1. Enter some numbers in cells A52:A60 and make some selections from the validation lists in cells B52:B60. You should now see some numbers in cells C52:D60.
  1. Enter a VLOOKUP Function in cell C64 to find the sales tax rate for the county selected in cell B47.  Use B47 as the first argument, the range name County as the middle argument, and 2 as the third argument.
  1. Enter a VLOOKUP Function in cell C65 to find the delivery fee for the county selected in cell B47. Use B47 as the first argument, the range name County as the middle argument, and 3 as the third argument.
  1. Enter a VLOOKUP Function in cell C68 to find the travel time for the county selected in Cell B47. Use B47 as the first argument, the range name County as the middle argument, and 4 as the third argument.
  1. Change the county in cell B47 and see what happens to the green cells C64 C65 and C68.

HLOOKUP Functions

There are no steps to complete in this section. This section is for information only.

If a table has data arranged in column instead of rows, with the labels in the first row, you would use an HLOOKUP function. The H means the instance names are listed horizontally.

 

F

G

H

I

J

71

Sales

Coffee

Juice

Tea

Total

72

Month1

$7,800

$9,000

$8,000

=SUM(G72:i72)

73

Month2

$9,000

$12,000

$15,000

=SUM(G73:i73)

74

Month3

$6,000

$10,500

$12,000

=SUM(G74:i74)

75

Average

=AVERAGE(G72:G74)

=AVERAGE(H72:H74)

=AVERAGE(i72:i74)

=AVERAGE(J72:J74)

76

Total

=SUM(G72:G74)

=SUM(H72:H74)

=SUM(i72:i74)

=SUM(J72:J74)

77

 

 

 

 

 

78

 

Sales of

 

 

 

79

 

 In Month One

=HLOOKUP(H78,G71:J76,2)

 

 

80

 

In Month Two

=HLOOKUP(H78,G71:J76,3)

 

 

81

 

In Month Three

=HLOOKUP(H78,G71:J76,4)

 

 

82

 

Quarterly Average

=HLOOKUP(H78,G71:J76,5)

 

 

83

 

Total for the Quarter

=HLOOKUP(h78,G71:J76,6)

 

 

 

In this table the instances are in row 71 and their attributes are in column F.

The input cell is H78, shaded in yellow. This is where we would enter a data validation list, using the words found in cells G71:J71 as the list items.

The HLOOKUP Functions are in the G79:G83 range, right below it: The arguments 2, 3, 4, 5, and 6 refer to the row in the table that includes the attribute we are looking for. In this example the 2 =row 72 in the worksheet, the Month1 attribute.

To use this form we would enter Coffee, Juice, Tea, or Total into cell G77, the input cell and press Enter.  The numerical data for our selected product be in cells G79, G80, G81, G82, and G83.

MATCH and INDEX Functions

Students

Age

City

Field

GPA

Beverly

22

Santa Clara

Management

3.61

Jon

20

Santa Cruz

Environmental Studies

3.72

Sue

18

San Jose

Pre-Law

3.43

 

Match() and Index()    6:15

 

The INDEX function is easier to explain. Let’s assume the table above is entered into the cells D85:H88

I want to find Jon’s GPA. I would enter this INDEX Function into a cell outside of the range.  =INDEX(D85:H88,3,4)

The arguments tell Excel to select the range D85:H88, and then select the 3rd row, and then select the 4th column. 

Jon is the name in the 3rd row and GPA is the value in the fourth column, so the INDEX function would show us the number 3.72.

The function =INDEX(D85:H88,2,5) would tell us that Beverly lives in Santa Clara.

INDEX functions are not very useful because you have to know ahead of time, when you write the function, which row and which column in the range are storing the particular piece of information that you want to find.

 

The MATCH function searches for a specified item (a word) in a range of cells and then returns the relative position (A number) of that item in the range

The MATCH Function has two arguments: the cell that contains the word to find, and the range in which to search for it.  =MATCH(A80,E85:H85) 

You can also use text strings for the first argument (“Age”) and a range name (Age) for the second argument.

Remember text strings are always surrounded by quotation marks and range names are not. 

The result of a match function is a number.

If you see the #N/A error checked the values in the range.

The text strings in the range must be arranged in alphabetical order.

 If the range contains numbers hey must be sorted in ascending or descending order.

 

41.  Select the range D86:D88. Enter Names in the name box. Press Enter.  

42.  Select the range E85:H85. Enter StudentData in the name box.  Press Enter.

43.  Enter a data validation list in cell B85 using the Names range for the allowed values.  Select one of the names.

44.  Enter a data validation list in cell B86 using the StudentData range for the allowed values.  Select one of the values.

45.  In cell B87, type =MATCH(B85,Names) 

You should see a number telling you which row in the range contains that student’s name.

46.  In B88, type =MATCH(B86, StudentData)

You should see a number telling you which column in the range contains the type of data you want to see.

47.  In cell B89, Type = INDEX(E86:H88,B87,B88)

You should see the data you were searching for. based on the selections you made in the previous two steps.

 

Although they are a bit more complicated to use, and require a few extra steps, there are a couple of advantages of using MATCH and INDEX instead of the VLOOKUP(HLOOKP) functions. 

·         A VLOOKUP function requires the lookup value to be in the first column or row of the lookup table. The INDEX and MATCH functions do not.

·         With VLOOKUP, inserting or deleting columns in the lookup table will ruin your formula.  Using a match function instead of a numerical value avoids this problem, because it looks for the column by name instead of by number. 

 

Review – Fine Points

 

Watch this video to review the skills covered in this lesson.  https://www.youtube.com/watch?v=iasekZJrCnU Time = 6:37

 

 

 

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel Lookup Functions

Y/N I can create a LOOKUP Function.

Y/N I can create a data validation list.

Y/N I understand what causes the #NA error and how to fix it.

Y/N I can create a VLOOKUP Function.

Y/N I can create an INDEX function

Y/N I can create a MATCH function