 # How to Use Excel Lookup Functions

How to pick the best lookup function in Excel. Compare VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH, and OFFSET

## VLOOKUP

The VLOOKUP function is a Vertical lookup.

• It looks down the left column of a range, to find a value.
• Then, it returns a value from another column in the range, from the row where the matching value was found.

### Example

In the following table, you could look for "East", and get the January sales, which is in the 2nd column of the range B4:E7.

=VLOOKUP(C1,B4:E7,2,0) • Simple to use, once you understand how it works.
• Can find an exact or approximate match

• It is a volatile function, and can slow down the calculations in a large workbook
• The value that you're searching for must be in the leftmost column of the range.

## HLOOKUP

The HLOOKUP function looks across the top row of a range, to find a value. Then, it returns a value from another row in the range, from the column where the matching value was found.

### Example

In the following table, you could look for "Jan", and get the West sales, which is in the 5th row of the range C3:E7.

=HLOOKUP(C1,C3:E7,5,0) • Simple to use, once you understand how it works.
• Can find an exact or approximate match

• It is a volatile function, and can slow down the calculations in a large workbook
• The value that you're searching for must be in the top row of the range.

## LOOKUP

The LOOKUP function has two syntax forms -- Vector and Array.

• With Vector form, it looks for a value in a specified column or row
• With Array form, it looks in the first row or column of an array, and returns the matching value from the last row or column.

### Example

This example uses LOOKUP in its Array form, with shift start times in column D, and shift numbers in column E.

When a factory machine breaks down, the incident start time is intered in column A, and this formula, in column B, calculates the shift number.

=LOOKUP(A4,\$D\$4:\$E\$7)

The formula finds an approximate match for the start time in the first column of the lookup table (D), and returns a value from that row, in the last column of the lookup table (E). • Simple to use
• Useful within other formulas
• Fast

• Approximate match only
• Lookup array or vector must be sorted in ascending order.

## INDEX / MATCH

The INDEX and MATCH functions can be combined to return a value from a range

• The INDEX function can return an item from a specific position in a list.
• The MATCH function can return the position of a value in a list
• MATCH can be used to find both the row and column position

### Example

In the following table, the MATCH function finds the row for East, and the column for Mar, and the INDEX function returns the value at that position.

=INDEX(B3:E7,MATCH(C1,B3:B7,0),MATCH(D1,B3:E3,0)) • Very flexible -- look for value in any row or column.
• Can find an exact or approximate match
• These are not volatile functions, so they have less impact on calculations in a large workbook

• Two functions required, and more difficult to understand and set up .

## OFFSET

The OFFSET function returns a reference, of a specified size, offset from the starting reference.

### Example

In the following table, you could get the value from the cell that is down 4 rows from cell B3.

=OFFSET(B3,C1,0) • Simple to use, once you understand how it works.
• Can return a range of more than one cell

• It is a volatile function, and can slow down the calculations in a large workbook

Download the Excel Lookup Functions workbook, to see all the examples from this page. The zipped file is in xlsx format, and does not contain macros.

## More Functions

CHOOSE

LOOKUP

Lookup - 2 Criteria