The HLOOKUP function in Excel is a tool used for searching for a specific value in a row and returning a value from a corresponding row in the same column. The "H" in HLOOKUP stands for "Horizontal," meaning that it looks for the value in rows rather than columns. HLOOKUP is designed to find data in a table by looking horizontally across the top row. Once it locates the matching data, it retrieves a value from the same column in a row you specify.
HLOOKUP Supported versions
The HLOOKUP function is a long-standing feature in Excel, supported across numerous versions of the software. It's available in the latest Microsoft Excel 365, as well as in the standalone versions like Excel 2019 and Excel 2016. Those using older versions such as Excel 2013, Excel 2010, and Excel 2007 can also access this function. The HLOOKUP function has been part of Excel for quite a while, so even if you're using much older versions, like Excel 2003 or Excel 2000, you'll still find it there.
HLOOKUP Syntax
The syntax for the HLOOKUP function in Excel is structured to search for a value in the top row of a table and return a value in the same column from a row you specify. Here's the breakdown of its syntax:
Arguments
| lookup_value | This is the value you are searching for. This is the value that HLOOKUP will look for in the first row of your table array. |
| table_array | This is the range of cells that contains the data you want to search. The function will look for the lookup_value in the first row of this array. |
| col_index_num | This is the row number in the table array from which to retrieve the value. For example, if you specify 2, 'HLOOKUP' will return the value from the second row of the table array. |
| [range_lookup] | It is optional but quite important. If you set it to TRUE or just leave it out, the function will look for the closest match to your specified lookup value. However, for this to work correctly, you need to make sure that the values in the first row of your table are in ascending order. On the other hand, if you set this argument to FALSE, HLOOKUP will search for an exact match of the lookup value. In this case, if it doesn’t find an exact match, the function will result in an error. This part of the function gives you control over how precisely HLOOKUP searches for your data. |
HLOOKUP Examples
Example 1: Exact Match with HLOOKUP
- Formula Setup: In this example, the formula is set to search for the value in cell O2, which contains "Alakazam", within a specified table range.
- Table Range: The range where the formula searches for "Alakazam" is defined as C2:L16. This means that Excel will look in the first row of this range (C2 to L2) for the value "Alakazam".
- [range_lookup] Argument: By setting the [range_lookup] argument to FALSE, you're instructing the HLOOKUP function to find an exact match of "Alakazam" in the first row of the table range. It’s important to note that when [range_lookup] is FALSE, the function expects the search value to exist in the first row. If the exact value isn’t found, the function will return an error.
- Returning the Value: If the function successfully finds "Alakazam" in the first row of the range C2:L16, it will then look in the same column, but in the 2nd row of the specified range, to return the corresponding value.
- Practical Use: This functionality can be particularly useful in scenarios where your data is organized in a horizontal format. For instance, if each column under the range C2:L16 represents different attributes of a list of items (with the names of the items in the first row), HLOOKUP can quickly retrieve specific information related to "Alakazam".
It’s important to ensure that the data in the first row of your table array is organized properly, as HLOOKUP will only search in this row for the lookup value. In addition, the row index number (in this case, 2) must correctly correspond to the row in your table array from which you want to retrieve the information.
Example 2: Approximate Match with HLOOKUP
In this example, we're focusing on how the HLOOKUP function operates within a specific table, specifically within the range of B2:L16. The functionality of the HLOOKUP function is significantly influenced by the [range_lookup] argument, particularly when it's set to TRUE.
- Table Range: The function is applied to the table located in the cell range B2:L16. Here, HLOOKUP will search for the specified value in the first row of this table.
- [range_lookup] Argument and Sorting: When the [range_lookup] argument is set to TRUE, it's important that the first row of your table is sorted, either numerically or alphabetically. This setting ensures that HLOOKUP operates correctly, as it's designed to find the closest match to the value you're searching for in a sorted list. If the data isn't sorted, the function might return inaccurate or unexpected results.
- Returning Value from the Third Row: Once HLOOKUP finds the closest match in the first row of the table, it will then retrieve the corresponding value from the third row in the same column where the match was found.
- Handling of Closest Matches: With [range_lookup] set to TRUE, if the exact value you're searching for isn't found, HLOOKUP will default to the nearest value that is less than or equal to your lookup value (provided the data is sorted as required). This can be particularly useful in situations where you need to approximate values or when an exact match isn't necessary.
Our formula setup is;
Similar Excel Formulas to HLOOKUP Function
In Excel, while functions like VLOOKUP , HLOOKUP , and LOOKUP are not case-sensitive, there are ways to perform case-sensitive searches using other functions or combinations of functions. (You can also check our 'Comparison of VLOOKUP, HLOOKUP, and LOOKUP' article for more information.)
For example, if your HLOOKUP function is searching for the word "Alakazam" in a row, it will consider "alakazam", "Alakazam", "ALAKAZAM", and "alAKaZaM" as the same. Regardless of how the letters are capitalized in your search term or in your data, HLOOKUP will treat them as matching. This characteristic is particularly useful when the capitalization of the data in your Excel sheet is inconsistent or when the capitalization of your lookup value is uncertain.
In contrast, a case-sensitive search would treat "Alakazam" and "alakazam" as different words, and it would only return a match if it found the exact same combination of uppercase and lowercase letters as in the search term. Excel does have functions and methods for case-sensitive searches, but HLOOKUP is not one of them. If we want to do a case-sensitive searches we can use:
- EXACT Function:
- The EXACT function compares two text strings and determines if they are exactly the same, including case sensitivity.
- Syntax:
EXACT(text1, text2) - For example,
=EXACT("Apple", "alakazam")would return FALSE because of the difference in capitalization.
- FIND Function:
- The FIND function can be used for case-sensitive searches within text. It locates one text string within another text string and returns the start position of the first text string from the first character of the second text string.
- Syntax:
FIND(find_text, within_text, [start_num]) - Unlike SEARCH, FIND is case-sensitive.
- Array Formulas (Advanced):
- For more complex case-sensitive lookups (like a case-sensitive
VLOOKUP), you can use an array formula. This might involve combining INDEX, MATCH, and EXACT functions. - Example:
=INDEX(return_range, MATCH(TRUE, EXACT(lookup_value, lookup_range), 0)) - This formula needs to be entered as an array formula (by pressing Ctrl+Shift+Enter in versions prior to Excel 365).
- For more complex case-sensitive lookups (like a case-sensitive
- SUMPRODUCT with EXACT (for Conditional Summing):
- SUMPRODUCT can be used in combination with EXACT for case-sensitive conditional summing.
- Example:
=SUMPRODUCT(--EXACT("Alakazam", range), sum_range)
HLOOKUP Function Tips
When working with the HLOOKUP function in Excel, especially when dealing with the [range_lookup] argument, there are a couple of important practices to keep in mind for effective and accurate results:
- Using a Sorted List with [range_lookup]:
- It's important to have your data sorted, either numerically or alphabetically, when you're either omitting the [range_lookup] argument or setting it to TRUE. This is because, in these cases, HLOOKUP is designed to find the closest match to your lookup value in a sorted list.
- If the [range_lookup] is omitted, Excel assumes it to be TRUE by default, meaning it will look for the nearest match. If your data isn’t sorted, the function might return the wrong value, leading to incorrect outcomes in your data analysis.
- Using Wildcards for Text Searches:
- HLOOKUP allows the use of Excel wildcards like '?' to represent any single character) and '*' (to represent any sequence of characters) when searching for text values. This can be incredibly useful if you’re looking for a text value that follows a certain pattern but you don’t have the exact term.
- For example, using
HLOOKUP("Ala*", ...)would search for any value that starts with "Ala". - However, we need to be careful about wildcard characters in numerical ranges. While wildcards are a powerful tool for text searches, they should be used cautiously, particularly in ranges containing numeric values. This is because wildcards are designed for text string searches, and using them in a numeric range may lead to incorrect or misleading results. For instance, if you’re looking for a specific number or a range of numbers, using a wildcard may not yield the desired results and could potentially disrupt the accuracy of your data analysis.
By adhering to these practices — ensuring sorted data for [range_lookup] TRUE or omitted and using wildcards appropriately — you can enhance the effectiveness of the HLOOKUP function in Excel. This will help maintain the integrity and accuracy of your data work, especially in scenarios where precise data retrieval is important.
| Operator | Description | Criteria Sample | Criteria Meaning |
| ? | Takes the place of a single character | “Ala?azam” | 8-character word starts with “Ala” and finishes with "azam" |
| * | Can take the place of any number of characters. | “Ala*” | Any number of character word starts with “Ala” |
| ~ | Use tilde in front of a question mark or an asterisk to actually find them | “Ala~*” | Equal to "Ala*" |
Common Issues and Errors in HLOOKUP
When using the HLOOKUP function in Excel, you might run into a few common issues. Here’s what they are and how you can fix them:
Wrong Value: If you don't set [range_lookup] or if you set it to TRUE, make sure the first row of your search range is sorted properly. HLOOKUP looks for the nearest match in a sorted list, so if it's not sorted, you might not get the correct value. To find an exact match of your search value, set [range_lookup] to FALSE. This tells Excel to look for an exact copy of what you're searching for in the first row.
#N/A! Error: If [range_lookup] is TRUE and your search value is smaller than the smallest value in the first row, Excel can't find a close match, so it shows a #N/A! error. If [range_lookup] is FALSE and the search value isn't exactly the same as any value in the first row, you'll also see a #N/A! error. Check your search value and the first row of your table to make sure they match how you've set [range_lookup].
#VALUE! Error: You'll get a #VALUE! error if your row index number (col_index_num) is less than 1. This number tells Excel which row to pull the data from, so it can't be less than 1. Make sure your row index number is 1 or more.
#REF! Error: A #REF! error happens if your row index number is greater than the number of rows in your table. Basically, Excel is being asked to look for a row that doesn’t exist. Adjust your row index number so it's within the range of your table.
Alternative Way for HLOOKUP Function but with Differences: XLOOKUP
XLOOKUP in Excel is a great alternative to HLOOKUP. It can do everything HLOOKUP does, and even more. If you usually use HLOOKUP to find things in rows, XLOOKUP can do that too, but it also has some extra helpful features. For example, XLOOKUP can give you a special answer if it can't find what you're looking for, and it can look up and down columns as well as across rows.
Although XLOOKUP and HLOOKUP are both LOOKUP functions in Excel, but they differ significantly in terms of flexibility, ease of use, and functionality. Let's have a look at them;
- Function Purpose:
- HLOOKUP: Horizontal Lookup. Searches for a value in the top row of a table or range and returns a value in the same column from a specified row.
- XLOOKUP: A more versatile lookup function introduced in Excel 365. It can search in any direction – vertically and horizontally – and offers more flexibility and power compared to HLOOKUP.
- Search Direction:
- HLOOKUP: Only searches horizontally (across rows).
- XLOOKUP: Can search both vertically (like VLOOKUP) and horizontally (like HLOOKUP).
- Syntax:
- HLOOKUP:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). - XLOOKUP:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
- HLOOKUP:
- Default Match Type:
- HLOOKUP: Approximate match by default, unless specified otherwise.
- XLOOKUP: Exact match by default.
- Return Value if Not Found:
- HLOOKUP: Returns an error if a match is not found.
- XLOOKUP: Allows you to specify a custom return value if a match is not found (
[if_not_found]argument).
- Handling of Array Formulas:
- HLOOKUP: Does not support dynamic arrays.
- XLOOKUP: Supports dynamic arrays in Excel 365 and Excel 2021, allowing it to return an array of values.
- Need for Sorted Data:
- HLOOKUP: Requires sorted data for an approximate match.
- XLOOKUP: Does not require sorted data for searching.
- Availability:
- HLOOKUP: Available in all modern versions of Excel.
- XLOOKUP: Only available in Excel 365 and Excel 2021.
