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)
Example 2: Using Approximate Match
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).
Handling Errors:
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.
