How to Look Up Values Horizontally in Excel

In Excel, you may often need to look up a value in a table and return a corresponding value from another row or column. For example, you may have a table of product names and prices, and you want to find the price of a specific product. This is called a horizontal lookup, because you are searching for a value in a row and returning a value from another row.

There are different ways to perform a horizontal lookup in Excel, depending on the structure of your data and the type of match you want. In this article, we will cover the following methods:

• Using the HLOOKUP function
• Using the XLOOKUP function
• Using the INDEX and MATCH functions

We will explain the basic theory behind each method, the procedures to apply them, and a comprehensive example with real data. We will also compare the advantages and disadvantages of each method and suggest when to use them.

Using the HLOOKUP Function

The HLOOKUP function is one of the built-in lookup functions in Excel. It stands for Horizontal Lookup, and it works as follows:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: The value you want to search for in the first row of the table.
• table_array: The range of cells that contains the table of data. The first row of the table must contain the lookup values.
• row_index_num: The row number in the table from which you want to return a value. The first row is 1, the second row is 2, and so on.
• [range_lookup]: An optional argument that specifies whether you want an exact match or an approximate match. If you omit this argument or set it to TRUE, the function will perform an approximate match, which means it will find the closest value that is less than or equal to the lookup value. If you set this argument to FALSE, the function will perform an exact match, which means it will find the exact value that matches the lookup value.

Procedure

To use the HLOOKUP function, follow these steps:

1. Identify the lookup value, the table array, the row index number, and the range lookup option for your data.
2. Enter the HLOOKUP function in a cell and provide the arguments as described above.
3. Press Enter to get the result.

Example

Suppose you have the following table of sales data for different products and regions:

You want to look up the sales of product C in the East region. To do this, you can use the HLOOKUP function as follows:

=HLOOKUP(“C”,A1:E5,4,FALSE)

• lookup_value: “C”, the product name you want to search for in the first row of the table.
• table_array: A1:E5, the range of cells that contains the table of data.
• row_index_num: 4, the row number in the table from which you want to return the sales value. The first row is 1, the second row is 2, and so on.
• [range_lookup]: FALSE, the option to perform an exact match.

The result is 190, which is the sales of product C in the East region.

Using the XLOOKUP Function

The XLOOKUP function is a new lookup function that was introduced in Excel 365. It is designed to replace the HLOOKUP and VLOOKUP functions, as well as the INDEX and MATCH combination. It has the following syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value you want to search for in the lookup array.
• lookup_array: The range of cells that contains the lookup values. It can be a row or a column.
• return_array: The range of cells that contains the return values. It must have the same size and shape as the lookup array.
• [if_not_found]: An optional argument that specifies what to return if the lookup value is not found. If you omit this argument, the function will return #N/A.
• [match_mode]: An optional argument that specifies the type of match you want. It can be one of the following values:
  • 0: Exact match (default)
  • -1: Exact match or next smaller
  • 1: Exact match or next larger
  • 2: Wildcard match
• [search_mode]: An optional argument that specifies the direction of the search. It can be one of the following values:
  • 1: First to last (default)
  • -1: Last to first
  • 2: Binary search (ascending)
  • -2: Binary search (descending)

Procedure

To use the XLOOKUP function, follow these steps:

1. Identify the lookup value, the lookup array, the return array, and the optional arguments for your data.
2. Enter the XLOOKUP function in a cell and provide the arguments as described above.
3. Press Enter to get the result.

Example

Using the same table of sales data as before, you want to look up the sales of product C in the East region. To do this, you can use the XLOOKUP function as follows:

=XLOOKUP(“C”,A2:A5,C2:C5)

• lookup_value: “C”, the product name you want to search for in the lookup array.
• lookup_array: A2:A5, the range of cells that contains the product names.
• return_array: C2:C5, the range of cells that contains the sales values for the East region.
• [if_not_found]: Not provided, the function will return #N/A if the lookup value is not found.
• [match_mode]: Not provided, the function will perform an exact match by default.
• [search_mode]: Not provided, the function will search from first to last by default.

The result is 190, which is the same as the HLOOKUP function.

Using the INDEX and MATCH Functions

The INDEX and MATCH functions are two separate functions that can be combined to perform a horizontal lookup. The INDEX function returns a value from a specific position in a range of cells, while the MATCH function returns the position of a value in a range of cells. By nesting the MATCH function inside the INDEX function, you can look up a value in a row and return a value from another row.

The syntax of the INDEX function is:

=INDEX(array, row_num, [column_num])

• array: The range of cells that contains the values you want to return.
• row_num: The row number in the array from which you want to return a value.
• [column_num]: The column number in the array from which you want to return a value. If you omit this argument, the function will return the entire row.

The syntax of the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value you want to search for in the lookup array.
• lookup_array: The range of cells that contains the lookup values. It can be a row or a column.
• [match_type]: An optional argument that specifies the type of match you want. It can be one of the following values:
  • 0: Exact match (default)
  • -1: Exact match or next smaller
  • 1: Exact match or next larger

Procedure

To use the INDEX and MATCH functions, follow these steps:

1. Identify the lookup value, the lookup array, the return array, and the match type for your data.
2. Enter the INDEX function in a cell and provide the array and the row number arguments as described above.
3. For the column number argument, enter the MATCH function and provide the lookup value, the lookup array, and the match type arguments as described above.
4. Press Enter to get the result.

Example

Using the same table of sales data as before, you want to look up the sales of product C in the East region. To do this, you can use the INDEX and MATCH functions as follows:

=INDEX(C2:C5,MATCH(“C”,A2:A5,0))

• array: C2:C5, the range of cells that contains the sales values for the East region.
• row_num: MATCH(“C”,A2:A5,0), the row number in the array from which you want to return a value. This is calculated by the MATCH function as follows:
  • lookup_value: “C”, the product name you want to search for in the lookup array.
  • lookup_array: A2:A5, the range of cells that contains the product names.
  • [match_type]: 0, the option to perform an exact match.

  The MATCH function returns 3, which is the position of “C” in the lookup array.

• [column_num]: Not provided, the function will return the entire row.

The result is 190, which is the same as the HLOOKUP and XLOOKUP functions.