Lookup functions

  • 1/31/2017
Contents
×
  1. Syntax of the lookup functions
  2. Answers to this chapter's questions
  3. Problems

In this chapter from Microsoft Excel Data Analysis and Business Modeling, 5th Edition, author Wayne Winston discusses the syntax of lookup functions in Microsoft Excel 2016.

Questions answered in this chapter:

  • How do I write a formula to compute tax rates based on income?

  • Given a product ID, how can I look up the product’s price?

  • Suppose that a product’s price changes over time. I know the date the product was sold. How can I write a formula to compute the product’s price?

Syntax of the lookup functions

Lookup functions enable you to “look up” values from worksheet ranges. Microsoft Excel 2016 allows you to perform both vertical lookups (by using the VLOOKUP function) and horizontal lookups (by using the HLOOKUP function). In a vertical lookup, the lookup operation starts in the first column of a worksheet range. In a horizontal lookup, the operation starts in the first row of a worksheet range. Because the majority of formulas using lookup functions involve vertical lookups, I’ll concentrate on VLOOKUP functions.

VLOOKUP syntax

The syntax of the VLOOKUP function is as follows. The brackets ([ ]) indicate optional arguments.

VLOOKUP(lookup value,table range,column index,[range lookup])

  • Lookup value is the value that you want to look up in the first column of the table range.

  • Table range is the range that contains the entire lookup table. The table range includes the first column, in which you try to match the lookup value, and any other columns in which you want to look up formula results.

  • Column index is the column number in the table range from which the value of the lookup function is obtained.

  • Range lookup is an optional argument. The point of range lookup is to allow you to specify an exact or approximate match. If the range lookup argument is True or omitted, the first column of the table range must be in ascending numerical order. If the range lookup argument is True or omitted and an exact match for the lookup value is found in the first column of the table range, Excel bases the lookup on the row of the table in which the exact match is found. If the range lookup argument is True or omitted and an exact match does not exist, Excel bases the lookup on the largest value in the first column that is less than the lookup value. If the range lookup argument is False and an exact match for the lookup value is found in the first column of the table range, Excel bases the lookup on the row of the table in which the exact match is found. If no exact match is obtained, Excel returns an #N/A (Not Available) response. In Chapter 12, you will learn how to use the IFERROR function to eliminate #N/A responses. Note that a range lookup argument of 1 is equivalent to True, whereas a range lookup argument of 0 is equivalent to False.

HLOOKUP syntax

In an HLOOKUP function, Excel tries to locate the lookup value in the first row (not the first column) of the table range. For an HLOOKUP function, use the VLOOKUP syntax and change column to row.

Let’s explore some interesting examples of lookup functions.

Save to your account

This chapter is from the book