Lookup

lookupHLOOKUP and VLOOKUP are functions in Microsoft Excel that allow you to use a section of your spreadsheet as a lookup table.

When the VLOOKUP function is called, Excel searches for a lookup value in the leftmost column of a section of your spreadsheet called the table array. The function returns another value in the same row, defined by the column index number.

HLOOKUP is similar to VLOOKUP, but it searches a row instead of a column, and the result is offset by a row index number. The V in VLOOKUP stands for vertical search (in a single column), while the H in HLOOKUP stands for horizontal search (within a single row).

VLOOKUP Example

Let's use the workbook below as an example which has two sheets. The first is called Data Sheet. On this sheet, each row contains information about an inventory item. The first column is a part number, and the third column is a price in dollars.

Lookup Example: Data Sheet

The second sheet is called Lookup Sheet, and it contains a formula which uses VLOOKUP to look up data on the Data Sheet. In the screenshot below, notice that cell B2 is selected, and its formula is listed in the formula bar at the top of the sheet.

Lookup Example: Lookup Sheet

The value of cell B2 is the formula =VLOOKUP(A2,'Data Sheet'!$A$2:$C$4,3,FALSE).

The above formula will populate the B2 cell with the price of the part identified in cell A2. If the price changes on the Data Sheet, the value of cell B2 on the Lookup Sheet will be updated automatically to match. Similarly, if the part number in cell A2 on the Lookup Sheet changes, cell B2 will automatically update with the price of that part.

Let's look at each element of the example formula in more detail.

Formula ElementMeaning
=The = (equals sign) indicates that this cell contains a formula, and the result should become the value of the cell.
VLOOKUPThe name of the function.
( An opening parenthesis indicates that the preceding name VLOOKUP was the name of a function, and indicates the beginning of a comma-separated list arguments to the function.
A2The first argument of the VLOOKUP function, known as the Lookup Value. It tells the function we want to look somewhere else for the value in cell A2. In this example, we want to look up part number P123.

This argument is required.
'Data Sheet'!$A$2:$C$4The second argument, the Table Array. It defines an area on a sheet to be used as the lookup table. The leftmost column of this area is the column that contains the Lookup Value.

The table array argument takes the general form:

'SheetName'!$col1$row1:$col2$row2

The first part of this expression identifies a sheet, and the second part identifies a rectangular area on that sheet. Specifically:

  1. SheetName is the name of the sheet where the table array (search area) is located. It should be enclosed in single quotes (' ') and followed by an exclamation mark (!). You only need a sheet identifier if you're looking up data on another sheet; if you omit the sheet identifier, VLOOKUP will attempt to perform the lookup on the same sheet as the function itself.
  2. col1, row1, col2, and row2 identify the upper left column, upper left row, lower right column, and lower right row of the table array, in that order. Each individual value is preceded by a dollar sign ($), and a colon (:) is used to separate the upper-left and lower-right sets of values.
The leftmost column of the table array must contain your lookup value. Always define your table array so that the leftmost column contains the value you're looking up.

This argument is required.
3The third VLOOKUP argument, the Column Index Number. It represents the number of columns, offset from the leftmost column of the table array, where the result of the lookup will be found. For instance, if the leftmost column of the lookup array is C, a Column Index Number of 4 would indicate that the result should come from column E.

In our example, the leftmost column of the Table Array is A and we want a result from column C. A is the first column, B is the second column, and C is the third column, so our column index number is 3.

This argument is required.
FALSEThe fourth argument is the Range Lookup value. It can be either TRUE or FALSE, and it specifies whether Excel should perform the lookup using "exact lookup" or "range lookup".

  • A value of TRUE means that Excel will perform a "range lookup", also known as a fuzzy match. A fuzzy mage means that the starts at the top row of the table array, searching down, one row at a time. If the value in that row is less than the lookup value (numerically or alphabetically), it proceeds to the next row and tries again. When it finds a value greater than the lookup value, it stops searching and takes its result from the previous row.
  • A value of FALSE means range lookup should not be performed. An exact match is required.
If you're not sure which type of match to use, choose FALSE for an exact match.

If you choose TRUE for a range lookup, make sure the data in the leftmost column of your table array is sorted in ascending order (least-to-greatest). Otherwise, the results will not be correct.

This argument is optional. If you omit this argument, an exact lookup will be performed.
)A closing parenthesis, which indicates the end of the argument list and the end of the function.

Remember:

  • The lookup value must be in the left-most column of the table array. If not, the lookup function will fail.
  • Make sure that every value in the left-most column of the table array is unique. If you have duplicate values in the column where the lookup takes place, the results of VLOOKUP are not guaranteed to be correct.

Also see: Excel, Formula