A Guide to Horizontal Lookup in Excel

Microsoft
1

The HLOOKUP function in Excel is a powerful tool for searching data organized horizontally. It allows you to find a value in a table or range by matching against data in a row, and then retrieving a corresponding value from another row. HLOOKUP is particularly useful when your data is structured with lookup values in the first row and associated data in the rows below.

Explanation of the Syntax

The syntax for the HLOOKUP function is as follows:

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

Where:

  • lookup_value is the value you want to search for. It can be text, a number, or a cell reference.

  • table_array is the range of cells where the lookup will be performed. This range must include the row containing the lookup_value and the row from which you want to retrieve the result.

  • row_index_num is the row number within the table_array from which the matching value will be returned. The topmost row in the table_array is row 1.

  • [range_lookup] is an optional argument that specifies whether to find an exact or approximate match. Using FALSE or 0 forces an exact match, which is generally recommended.

How to use HLOOKUP

Step 1: Open your Excel spreadsheet.

Step 2: Select the cell where you want the result of the HLOOKUP function to appear.

Step 3: Enter the HLOOKUP formula in the cell.

For example, if you want to find the age of a person with a specific ID from a table where the IDs are in the first row (A1:D1) and the ages are in the second row (A2:D2), you would use a formula similar to:

=HLOOKUP("123", A1:D2, 2, FALSE)

In this example, "123" is the lookup_value (the ID you are searching for), A1:D2 is the table_array (the range containing the IDs and ages), and 2 is the row_index_num (the row containing the ages). FALSE specifies that you want an exact match.

Step 4: Press Enter to execute the formula.

The cell will display the age corresponding to the ID “123”, assuming an exact match is found in the specified table_array. If no exact match is found and you’ve set range_lookup to FALSE, the formula will return a #N/A error.

Alternative Method: INDEX and MATCH

While HLOOKUP is useful, the combination of INDEX and MATCH offers greater flexibility.

Explanation of INDEX and MATCH Syntax

=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))

  • return_array: The range containing the value to return.
  • lookup_value: The value you are searching for.
  • lookup_array: The range to search within.
  • 0: Specifies an exact match.

Step 1: Open your Excel spreadsheet.

Step 2: Select the cell where you want the result of the INDEX and MATCH combination.

Step 3: Enter the INDEX and MATCH formula in the cell.

For example, if you want to find the age of a person with a specific ID, where the IDs are in the first row (A1:D1) and the ages are in the second row (A2:D2), the formula would be:

=INDEX(A2:D2, MATCH("123", A1:D1, 0))

In this example, A2:D2 is the return_array (the range containing the ages), "123" is the lookup_value (the ID you’re searching for), and A1:D1 is the lookup_array (the range containing the IDs).

Step 4: Press Enter to execute the formula.

Alternative Method: XLOOKUP

XLOOKUP is a more modern and versatile function that addresses many of the limitations of HLOOKUP. It doesn’t require the lookup row to be in a specific position and offers better handling of errors and default values.

Explanation of the XLOOKUP Syntax

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

  • lookup_value: The value you want to search for.
  • lookup_array: The range to search within.
  • return_array: The range containing the value to return.
  • [if_not_found]: Optional value to return if no match is found.
  • [match_mode]: Optional match type (0 for exact match).
  • [search_mode]: Optional search order.

Step 1: Open your Excel spreadsheet.

Step 2: Select the cell where you want the result of the XLOOKUP function.

Step 3: Enter the XLOOKUP formula in the cell.

For example, to find the age of a person with a specific ID, with IDs in row A1:D1 and ages in row A2:D2, the formula would be:

=XLOOKUP("123", A1:D1, A2:D2, "Not Found", 0)

Here, "123" is the lookup_value, A1:D1 is the lookup_array, A2:D2 is the return_array, "Not Found" is the value to return if no match is found, and 0 specifies an exact match.

Step 4: Press Enter to execute the formula.

While HLOOKUP can be useful, XLOOKUP often provides a more flexible and robust solution for horizontal lookups in Excel.