# How to Use Excel Lookup Functions

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

7 Ways to Lookup

VLOOKUP

HLOOKUP

LOOKUP

MATCH

INDEX

## 7 Ways to Lookup

To see a quick overview of 7 ways to lookup in Excel, watch this short slide show, or details and step by step instructions, below the slide show.

Also, you can download the Excel Lookup Functions sample workbook, and follow along with the slide show.

## VLOOKUP

The VLOOKUP function 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.

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

CHOOSE

LOOKUP

INDIRECT

## Search Contextures

Search Contextures Sites

Search Contextures Sites

Last updated: August 24, 2017 3:25 PM