Contextures

How to Use Excel Lookup Functions

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

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)

vlookup value left column

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)

hlookup value top row

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.

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

index match flexible lookup

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)

offset specific rows and columns

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 sample Compare Lookup Functions workbook

More Functions

CHOOSE

LOOKUP

HYPERLINK

INDIRECT

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

Search Contextures Sites

 

Excel Tools Add-in

 

 

Peltier Tech Charts for Excel 3.0

 

 

 

Excel Data Entry Popup List

 

Excel UserForms for Data Entry

 

Last updated: December 25, 2016 3:35 PM
Contextures RSS Feed