To match a value across the top row of a range in Excel, use HLOOKUP and set its fourth argument to FALSE for an exact match. With lookup value in G1, headers in B1:E1, and results in B2:E2, enter =HLOOKUP(G1,B1:E2,2,FALSE); Excel finds G1 in the top row and returns the value from the second row of the range.
HLOOKUP is useful when a worksheet is arranged horizontally, such as months across columns and figures in rows beneath them. Its match setting determines whether Excel requires the lookup value to appear exactly or can use the closest lower value. Knowing which setting fits the data prevents many silent lookup errors.
What HLOOKUP matches
HLOOKUP means horizontal lookup. It searches the first, or top, row of a selected range from left to right. After finding the lookup value, it returns a value from another row in the same column. The function does not search every row: the lookup row must be the first row of the table_array you provide.
For example, a worksheet might have product codes across row 1, product names across row 2, and prices across row 3. HLOOKUP can locate a code in row 1 and return the corresponding price from row 3. If the codes instead run down a column, VLOOKUP or another lookup method is usually a better fit.
HLOOKUP returns a value, not a position or a whole record. Its row number is counted within the selected range, not by the worksheet’s row labels. If the range starts at row 1, row_index_num 3 means worksheet row 3; if the range starts at row 5, row_index_num 3 means worksheet row 7.
HLOOKUP syntax and its arguments
The function has four arguments. The first three are required, and the fourth controls the match type:
- lookup_value: the value to search for, such as a month, code, or number.
- table_array: the cells containing the lookup row and the row from which to return a result. The lookup row must be at the top of this range.
- row_index_num: the row to return from within table_array. The top row is 1, the next row is 2, and so on.
- range_lookup: FALSE requests an exact match; TRUE requests an approximate match. If this argument is omitted, Excel uses TRUE.
That default is important: leaving out the fourth argument does not mean exact match. For unsorted labels, codes, names, or identifiers, explicitly enter FALSE. The row_index_num must be at least 1 and no greater than the number of rows in table_array.
Enter the function in a cell and press Enter, or select the cell, choose the Insert Function button beside the formula bar, and search for HLOOKUP. The same function and argument order apply in Excel for Microsoft 365 on Windows and Mac. Excel for the web also supports HLOOKUP, though some formula-entry controls may appear differently.
Exact match with FALSE
FALSE tells Excel to find the requested value as an exact match in the top row. If no matching value is present, HLOOKUP returns #N/A. Exact match is generally the safest choice for categories and identifiers because it does not depend on the order of the headers.
Suppose B1:E1 contain Q1, Q2, Q3, and Q4, while B2:E2 contain 180, 240, 210, and 300. With those headers and results in B1:E2 and the lookup value Q2 entered in G1, the formula =HLOOKUP(G1,B1:E2,2,FALSE) returns 240. The 2 tells Excel to return from the second row of the selected range.
For a fixed lookup table copied to other cells, absolute references can keep the range from shifting. With the same table in B1:E2 and Q2 in G1, use =HLOOKUP(G1,$B$1:$E$2,2,FALSE). The dollar signs lock the table range when the formula is copied; they do not change how HLOOKUP matches. In Windows Excel, F4 can cycle through reference styles while editing or selecting a reference in a formula. On Mac, the equivalent shortcut depends on the keyboard and system settings; you can also type the dollar signs directly.
HLOOKUP is not case-sensitive: searching for Q2 can match q2. In an exact text lookup, the wildcard characters * and ? can also be used, where * represents any number of characters and ? represents one character. To search for a literal asterisk or question mark, precede it with a tilde (~). These wildcard rules are useful for text patterns, but they are not a substitute for matching a unique identifier exactly.
Approximate match with TRUE
TRUE asks Excel for an approximate match. In ordinary use, Excel returns the value associated with the largest entry in the top row that is less than or equal to the lookup value. This is useful for interval tables, such as tax bands, shipping thresholds, grading cutoffs, or commission rates. For this result to be reliable, the top row must be sorted in ascending order.
For example, let B1:E1 contain 0, 50, 100, and 150, and let B2:E2 contain 0%, 5%, 10%, and 15%. With the thresholds and rates in B1:E2, this formula searches for 120: =HLOOKUP(120,B1:E2,2,TRUE). It returns 10%, because 100 is the greatest threshold not exceeding 120. A lookup value below the first threshold has no eligible lower threshold and returns #N/A.
Approximate match is not simply “find something close.” It follows the ordered-threshold rule, so the table must be designed around that rule. If the thresholds are out of order, a result may be incorrect even if it looks plausible. For text thresholds, ascending order must also be appropriate to the values being compared; when in doubt, use exact match or test the boundary values explicitly.
TRUE is the default if range_lookup is left blank, but spelling it out makes the intended behavior clear to someone reviewing the workbook. Likewise, do not use approximate matching for product codes or names just because the function returns a result: a nearby value is not necessarily a valid match.
Build a reliable horizontal lookup
Before entering the function, decide what each row and column represents. Keep lookup values together in the first row of the selected range, and place the result rows directly underneath in matching columns. Avoid including unrelated rows above the headers, because HLOOKUP searches the first row of the range you select, not a row it identifies as a header.
- Choose the value to find and confirm its type, such as text or a number.
- Select a range whose top row contains the lookup values and whose lower rows contain the results.
- Count the return row from the top of that selected range.
- Use FALSE for exact categories and identifiers, or TRUE for properly sorted thresholds.
- Test a value that should match, one that should not match, and—if using approximate matching—values at and between the thresholds.
For an exact lookup, check that the lookup value and the header are genuinely the same. A number stored as text may not match a numeric value, and extra spaces can make text labels appear identical while differing underneath. If data comes from imports, inspect the source cells or use a separate cleanup step rather than changing the match mode to hide the mismatch.
Also check for duplicate headers. HLOOKUP returns a result for a matching entry in the first row, but duplicate keys make the intended result ambiguous. If each key should identify one record, remove or resolve duplicates in the source data so the formula’s answer has a clear meaning.
Common errors and how to diagnose them
#N/A means an exact match was not found, or an approximate lookup had no eligible value. Check spelling, spaces, number-versus-text differences, the selected range, and whether the lookup value actually appears in the top row. In approximate mode, also confirm that thresholds are sorted ascending and that the lookup value is not below the first threshold.
#REF! commonly points to a row_index_num beyond the number of rows in table_array. For example, asking for row 3 from a two-row range is invalid. #VALUE! can occur when row_index_num is less than 1 or is not a usable number. If Excel displays the formula as text rather than calculating it, check whether the cell is formatted as Text and whether the formula begins with an equals sign.
A frequent logic error is selecting the wrong table range. If the top row of the selected range contains titles rather than the keys, HLOOKUP will search those titles and not the row below. Another is counting the return row using worksheet row numbers instead of counting from the top of table_array. Revisiting the range and row index often resolves errors more quickly than rewriting the entire formula.
If you want a friendlier message when an exact value is missing, you can wrap the lookup in IFNA. With the same lookup value in G1 and lookup range B1:E2, =IFNA(HLOOKUP(G1,B1:E2,2,FALSE),"Not found") displays Not found only when the lookup produces #N/A. It will not conceal other errors such as an invalid row index, which is often helpful while diagnosing a workbook.
HLOOKUP compared with other lookup options
HLOOKUP is appropriate when the lookup keys are laid out horizontally and the worksheet needs to remain compatible with versions that do not have newer lookup functions. Its main structural limitation is that it searches only the top row of its table range and returns a row beneath it. It cannot naturally look to the left or return a value from a row above the lookup row.
XLOOKUP can perform horizontal lookups by receiving a row to search and a corresponding row to return. It defaults to exact match and can return a custom value when nothing is found. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel versions. With headers in B1:E1, results in B2:E2, and Q2 in G1, =XLOOKUP(G1,B1:E1,B2:E2,"Not found") returns 240 for the example data above.
INDEX with MATCH is another alternative and works in older Excel editions. MATCH finds the position of a value in a row, and INDEX returns the value at that position in the results row. With the same headers in B1:E1, results in B2:E2, and Q2 in G1, =INDEX(B2:E2,MATCH(G1,B1:E1,0)) returns 240. The 0 in MATCH requests an exact match. This approach is more flexible than HLOOKUP when the return row is above or otherwise separate from the lookup row, but it is less compact for a simple horizontal table.
Changing a horizontal layout into a vertical one can also make a dataset easier to extend and use with VLOOKUP or other tools, but rearranging data is not required just to make HLOOKUP work. Choose based on the worksheet’s structure, the Excel versions other people need to use, and whether the function’s direction limits the task.
Frequently Asked Questions
Does HLOOKUP default to an exact match?
No. If you omit range_lookup, Excel uses TRUE for approximate matching. Add FALSE explicitly when you need an exact match, especially for labels, product codes, and other identifiers.
Why does HLOOKUP return #N/A even though the value looks identical?
The cells may differ in a way that is hard to see: one might contain a number stored as text, a leading or trailing space, or a different character. Confirm the lookup value is in the selected range’s top row and compare the underlying cell contents.
Can HLOOKUP return a value from a row above the match?
No. HLOOKUP searches the top row of table_array and returns from a row below it. Use INDEX and MATCH or XLOOKUP when the return row needs to be above the lookup row.
When should I use TRUE instead of FALSE?
Use TRUE for ordered thresholds where the closest value at or below the lookup value defines the result. Sort the top row ascending and verify boundary cases. For ordinary exact labels or identifiers, use FALSE.
Is HLOOKUP case-sensitive?
No. Uppercase and lowercase text are treated as equivalent by HLOOKUP. If capitalization must distinguish two entries, use a different approach designed for case-sensitive comparison.
Related
- Data Cleaning Tools in Excel: What to Use and When
- Data Cleaning in Excel: A Practical Example
- Data Cleaning Shortcuts in Excel: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Criteria Examples: Text, Dates, Numbers, and MoreIn Excel, SUMIF adds values that match one condition; for example, with categories in A2:A7 and amounts in…
- Excel Julian Date Formula: Day of Year and MoreFor a date in A2, use =INT(A2)-DATE(YEAR(A2),1,0) to return its day number within the year: January 1 is…
- Excel Formula for Counting Unique ValuesIn Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) to count distinct nonblank values in A2:A100. It returns 0 if…
- COUNTIF Excel Blank? Count Empty Cells CorrectlyTo count blank cells in Excel, use =COUNTIF(A2:A20,""), replacing A2:A20 with your range. COUNTIF can also count cells…
- COUNTIFS in Excel: How to Count OR CriteriaCOUNTIFS in Excel uses AND logic: a row is counted only when it meets every condition you supply.…
- How to Count Unique Values in a Row in Excel EasilyTo count distinct, nonblank values across a row in Microsoft 365, use =IFERROR(COLUMNS(UNIQUE(FILTER(B2:K2,B2:K2<>""))),0). This assumes the values are…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.