HLOOKUP Examples in Excel: Formulas and Common Fixes

In Excel, HLOOKUP searches across the first row of a range and returns a value from a specified row beneath the matching column. For example, with quarter names in A1:D1 and sales in A2:D2, =HLOOKUP("Q2",A1:D2,2,FALSE) finds Q2 and returns the value below it; FALSE requests an exact match.

How HLOOKUP works

HLOOKUP is short for horizontal lookup. Its syntax is =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]). The arguments tell Excel what to find, where to search, which row in the range to return, and whether to use an exact or approximate match.

lookup_value: The item to find, such as a quarter name, product code, or a reference to a cell containing that item.
table_array: The complete range that contains the lookup row and the values to return. HLOOKUP searches only the range’s top row.
row_index_num: The number of the return row within the selected range. The top row is 1, the next row is 2, and so on.
range_lookup: Use FALSE for an exact match or TRUE for an approximate match. If you leave this argument out, Excel uses TRUE.

HLOOKUP searches from left to right across the first row of the range, then returns a value from the same column in the row you specify. It does not search down a column, and the row number is counted from the top of the selected range—not from the worksheet’s row numbers. If the range begins at row 5, for instance, its first row still has index 1.

Example: Find a quarter’s sales

Suppose A1:D2 contains quarter labels in the first row and sales in the second: A1:D1 has Q1, Q2, Q3, Q4, while A2:D2 has 12000, 14500, 13800, 16200. To return sales for Q3, the formula is:

With quarter labels in A1:D1 and sales in A2:D2, use:

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

The result is 13800. Excel finds Q3 in the top row, moves down to row 2 of the selected range, and returns the value in the matching column. The 2 in the formula means the second row of A1:D2. It does not mean worksheet row 2 by coincidence; it means the second row relative to the range.

Using a cell reference makes the lookup easier to reuse. If F1 contains Q3, the formula below returns the same result:

With the same data in A1:D2 and the lookup quarter in F1, use:

=HLOOKUP(F1,A1:D2,2,FALSE)

Change F1 to Q1 or Q4 and the formula returns that quarter’s sales. If the label in F1 is not present in the first row, an exact-match formula returns #N/A.

Example: Return a different row

A single horizontal table can hold several kinds of information beneath its column headings. Suppose A1:D3 contains quarter labels in row 1, units sold in row 2, and revenue in row 3. The values are Q1 through Q4, then 120, 145, 138, 162, then 2400, 2900, 2760, 3240. To retrieve revenue for Q2:

With quarter labels in A1:D1, units in A2:D2, revenue in A3:D3, and the requested quarter in F1, use:

=HLOOKUP(F1,A1:D3,3,FALSE)

If F1 contains Q2, the result is 2900. The row index is 3 because revenue is on the third row of the selected range. To return units instead, change the index to 2; the result for Q2 would be 145. This is a common source of mistakes: selecting a three-row range but asking for row 4 produces #REF! because that row is outside the range.

When copying a formula to other cells, lock the data range with dollar signs so it does not shift. With the same layout and lookup value in F1, use:

=HLOOKUP(F1,$A$1:$D$3,3,FALSE)

The dollar signs make the range absolute. They are especially useful when filling formulas down a list of lookup values. The lookup reference F1 remains relative here, so it changes to F2, F3, and so on as the formula is copied down.

Example: Approximate match for score bands

Approximate matching is useful when the top row contains ascending breakpoints rather than unique labels. For example, A1:E2 could contain minimum score thresholds 0, 60, 70, 80, 90 in row 1 and grades F, D, C, B, A in row 2. A score of 84 belongs to the band starting at 80:

With minimum scores sorted in ascending order in A1:E1, grades in A2:E2, and a score of 84 in G2, use:

=HLOOKUP(G2,A1:E2,2,TRUE)

The result is B. With TRUE, Excel selects the largest first-row value that is less than or equal to the lookup value. For 84, that breakpoint is 80. In this example, the values in the first row must be sorted from smallest to largest for the approximate result to be reliable. If the score is below the first breakpoint, HLOOKUP returns #N/A.

Use approximate match only when the first row represents ordered ranges or thresholds. For names, product IDs, dates used as labels, and most ordinary lookups, FALSE is safer. Leaving the final argument blank does not mean “exact match”; it makes HLOOKUP use its approximate-match behavior.

Example: Use a wildcard in a header

With an exact-match setting, HLOOKUP can also use wildcards to match text labels that share a pattern. The asterisk represents any number of characters, and the question mark represents one character. A tilde can escape a wildcard when you need to search for the literal character.

Suppose A1:C2 has headers North-West, South, and North-East in row 1, with sales of 500, 350, and 620 in row 2. This formula finds the first header beginning with North:

With those region headers in A1:C1 and sales in A2:C2, use:

=HLOOKUP("North*",A1:C2,2,FALSE)

The result is 500, because North-West is the first matching header from left to right. A wildcard lookup does not combine all matching regions; it returns only the value associated with the first matching column. HLOOKUP is also not case-sensitive, so capitalization differences do not create separate matches.

Common HLOOKUP errors and fixes

When a formula does not return the expected value, check the lookup row, range boundaries, and match setting before changing the data. These are the most frequent problems:

#N/A: With FALSE, Excel cannot find an exact match in the first row. Check for extra spaces, inconsistent text or numbers, and whether the lookup value is actually in the selected range. With TRUE, #N/A can mean the lookup value is smaller than the smallest threshold.
#REF!: The row index is greater than the number of rows in the selected range, or the formula refers to a deleted range. Count the selected range’s rows and adjust the index.
#VALUE!: The row index is less than 1 or an argument is not usable. Confirm that the row index is a positive number.
Unexpected approximate result: The final argument may be missing, or the top row may not be sorted ascending. Add FALSE for exact matching or sort threshold values from low to high when TRUE is intentional.
Wrong column returned: The lookup value may appear more than once. HLOOKUP returns the first match from left to right, so check for duplicate headers or narrow the range.

Text that looks identical may still fail an exact match if one cell contains a trailing space or a nonbreaking space copied from another source. Numbers stored as text can also differ from numeric values. If the data comes from an export, inspect the source cells and standardize the values rather than assuming the visible formatting guarantees identical underlying contents.

Make formulas easier to maintain

Keep the lookup row and the return rows together in a clearly defined range. A lookup range that starts too far left or right can produce a plausible result from the wrong column, while a range that excludes a return row can cause #REF!. When formulas are copied or used repeatedly, absolute references such as $A$1:$D$3 keep the table range fixed.

For a regularly changing dataset, check that new columns are included in the lookup range. A formula referencing A1:D3 will not automatically include a new header entered in column E. Excel tables can expand as rows are added, but HLOOKUP still requires a range whose top row contains the headers it must search; it does not automatically transform a vertical list of records into a horizontal lookup table.

Consider what should happen when a header is missing. HLOOKUP with FALSE returns #N/A, which can be useful because it exposes a mismatch. If a worksheet needs a friendlier display, an error-handling function such as IFNA can be added, but the underlying lookup should still be checked so a genuine data problem is not hidden.

HLOOKUP compared with other lookup options

HLOOKUP is appropriate when the lookup keys run horizontally across the first row and the answers are below them. Its main limitation is that the return row is specified by a number. Inserting or rearranging rows can make a fixed row index point at a different type of result, so the formula may need updating.

XLOOKUP is a more flexible option in Microsoft 365 and Excel 2021 or later. It can search one range and return a corresponding range, including for a horizontal lookup, and it uses exact matching by default. For example, if Q2 is in F1, quarter labels are in A1:D1, and units are in A2:D2:

With quarter labels in A1:D1, units in A2:D2, and the requested quarter in F1, use:

=XLOOKUP(F1,A1:D1,A2:D2,"Not found")

If F1 contains Q2, the result is 145 when A2:D2 contains 120, 145, 138, 162. XLOOKUP is not available in every older Excel release, so HLOOKUP can be useful when a workbook must support older versions. INDEX and MATCH can also handle flexible lookup layouts, but HLOOKUP may be easier to read for a small, genuinely horizontal table.

Frequently Asked Questions

What does the 2 mean in an HLOOKUP formula?

It means return a value from the second row of the selected table range. The count starts at the range’s top row, which is row 1, not at the worksheet’s row number.

Should I use TRUE or FALSE in HLOOKUP?

Use FALSE for an exact match, which is the usual choice for labels and codes. Use TRUE for approximate matching against ascending breakpoints, such as minimum scores or tax-style bands, and make sure the first row is sorted from smallest to largest.

Why does HLOOKUP return #N/A?

With FALSE, #N/A means Excel did not find the lookup value in the first row of the selected range. Check that the range includes the right headers and that the lookup value has no extra spaces or text-versus-number mismatch.

Can HLOOKUP search for a value below the headers?

No. HLOOKUP always searches the first row of its table range. If the lookup keys are arranged vertically in the first column, use VLOOKUP or another function designed for that layout.

Does HLOOKUP work in Excel for Mac and Excel for the web?

HLOOKUP is available in current Excel for Windows, Excel for Mac, and Excel for the web. The formula syntax and lookup behavior are the same; the main differences are in the surrounding interface, not in this function.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.