Search Contextures Sites
Custom Search

Excel Lookup Functions

Compare some of the lookup functions that are available in Excel, and see the advantages and disadvantages of each function.

VLOOKUP

HLOOKUP

INDEX / MATCH

OFFSET

Download the Sample File

More Functions

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

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

 

 

 

More Functions

 

30 Excel Functions in 30 Days

 

 

 

 

 

 

 

 

 

 

 

 

 

__

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: June 5, 2016 10:39 AM