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)

Advantages

• Simple to use, once you understand how it works.
• Can find an exact or approximate match

Disadvantages

• 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.

More Information

Click the link to read more about the VLOOKUP function, and see other examples, and get a download file.

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)

Advantages

• Simple to use, once you understand how it works.
• Can find an exact or approximate match

Disadvantages

• 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.

More Information

Click the link to read more about the HLOOKUP function, and see other examples, and get a download file.

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).

Advantages

• Simple to use
• Useful within other formulas
• Fast

Disadvantages

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

More Information

Click the link to read more about the LOOKUP functions, and see other examples, and get a download file.

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))

Advantages

• 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

Disadvantages

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

More Information

Click the link to read more about the INDEX / MATCH functions, and see other examples, and get a download file.

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)

Advantages

• Simple to use, once you understand how it works.
• Can return a range of more than one cell

Disadvantages

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

More Information

Click the link to read more about the OFFSET function, and see other examples, and get a download file

Download the Sample File

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

HYPERLINK

INDIRECT

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.