Home > Formulas > Lookup > Compare

How to Use Excel Lookup Functions

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

LOOKUP function

Authors: Debra Dalgleish, Alex Blakenburg

XLOOKUP Function

The XLOOKUP function is a two-way lookup that will work either as a vertical or horizontal lookup.

NOTE: XLOOKUP is only available in MS 365 or Excel 2021 or later.

  • It looks in a specified column (vertical) or across a specified row (horizontal), to find a valid match.
  • It will return:
    • Vertical lookup:
      • If the return array is a single column, it will return a single cell.
      • If the return array is multiple columns, the row
    • Horizontal lookup:
      • If the return array is a single row, it will return a single cell.
      • If the return array is multiple rows, the column
    • Or specify a value to return if a valid match is not found

Example

In the example shown below, the following Excel XLOOKUP function is in cell G2:

=XLOOKUP($F$2,  tblProductsXLK2[Code],  tblProductsXLK2[Item],  "Not Found")

  1. first argument is lookup value - what value to look for (code entered in cell F2)
  2. second argument is the lookup range - where to look for the code (Code column)
  3. third argument is the return range - where to find the item name (Item column)
  4. fourth argument (optional)  is the result to return if valid match is not found
  5. 5th and 6th arguments (optional) were not used, so the default settings for those arguments were used.

In cell G2, the formula result is "Sweater", from worksheet row 5, where the code "SW001" was found.

vlookup value left column

Advantages

  • Very flexible -- use XLOOKUP to find lookup value in any lookup range, row or column.
  • Match modes can specifiy an exact match or approximate match type
  • Search mode can find first item or last item (reverse search) that matches
  • Binary search mode for lists sorted in ascending or descending order
  • By using 2 XLOOKUP functions can find both the row and the column
  • Can return multiple cells using SPILL
  • Can use multiple criteria
  • Combine * (asterisk) or ? (question mark) with ~ (tilde) to find as a character, or use them as a wildcard match for partial matches.
  • Result is a cell or range, so there is the option to use the address in another formula such as SUM, instead of simply returning the value from the cell(s).
  • These are not volatile functions, so they have less impact on calculations in a large workbook

Disadvantages

  • XLOOKUP formula only available in MS 365 or Excel 2021 or later version of Excel
  • Using 2 XLOOKUP functions in a single formula, for finding row and column is less intuitive than the similar INDEX-MATCH-MATCH function

More Information

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

VLOOKUP Function

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)

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 Function

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.

LOOKUP Function

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

LOOKUP function

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 Functions

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 Function

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

Logical Functions

Lookup - 2 Criteria

HYPERLINK

INDIRECT

Excel Lookup Function Humour

Did Excel's lookup formulas ever make you want to cry? Don't worry -- you're not alone!

My Excel tears are 30% lookup formulas

 

 

Last updated: March 8, 2024 12:50 PM