HLOOKUP with easy-to-understand examples

HLOOKUP is a function in spreadsheet software like Microsoft Excel or Google Sheets that stands for “Horizontal Lookup.” It is used to search for a value in the first row of a range and return a value in the same column from a specified row. Here’s a comprehensive guide with easy-to-understand examples:

Formula Syntax

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

• lookup_value: The value to search for in the first row of the range.
• table_array: The range of cells that contains the data.
• row_index_num: The row number in the table_array from which to retrieve the value.
• range_lookup: Optional. If TRUE (or omitted), it assumes an approximate match. If FALSE, it looks for an exact match.

Example 1: Basic Usage of HLOOKUP

Consider the following table:

=HLOOKUP(“Mary”, A1:D3, 2, FALSE)

Consider a similar table, but this time, let’s use an approximate match for the age.

=HLOOKUP(26, A1:D3, 2, TRUE)

Explanation:

lookup_value: 26

table_array: A1:D3

row_index_num: 2

range_lookup: TRUE (approximate match)

The function returns “John” because it finds the value just less than 26 in the first row (John’s age is 25).

If the lookup_value is not found, HLOOKUP returns an error. You can use the IFERROR function to handle this:

=IFERROR(HLOOKUP(“Alex”, A1:D3, 2, FALSE), “Not found”)

This formula will return “Not found” if the name “Alex” is not in the table.

Example 2:

I hope these examples help you understand how to use HLOOKUP in different scenarios!

Let’s consider another example where you want to find the city of a person based on their grade. Suppose you have the following table:

Now, you want to find the city for someone with Grade “B.” You can use HLOOKUP as follows:

=HLOOKUP(“B”, A1:D4, 4, FALSE)

Explanation:

lookup_value: “B”

table_array: A1:D4

row_index_num: 4 (since the data is in the fourth row, which is the “City” row)

range_lookup: FALSE (exact match)

The function returns “LA,” which is the city corresponding to Grade “B.”

This example illustrates how you can use HLOOKUP to search for information in a horizontal table based on a specific criterion.