HLOOKUP in Excel With Multiple Criteria: A Clear Guide

Formulas & functions · Updated November 18, 2026

HLOOKUP does not accept separate criteria on its own, but you can make it work by combining the criteria into one helper row and looking up that combined key. For a horizontal table, this formula returns the sales value for a matching product, region, and month: =HLOOKUP($J$1&"|"&$J$2&"|"&$J$3,$B$1:$G$5,5,FALSE). The examples below show how to set up the rows, handle errors, and choose a formula that fits your workbook.

How HLOOKUP handles a lookup

HLOOKUP searches across the first row of a selected range. Once it finds a match, it returns a value from a specified row in the same column. Its syntax is HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). The last argument controls whether Excel looks for an exact match: use FALSE for an exact match, as in the examples here.

The function is designed for one lookup value in one row. It cannot be given instructions such as “find the column where the product is Widget, the region is West, and the month is Jan” as three independent tests. A common solution is to create a single key from those fields. HLOOKUP can then search for that key in its top row.

This approach suits data arranged horizontally: each record or combination occupies a column, and different fields occupy rows. If each record occupies a row instead, VLOOKUP, XLOOKUP, or INDEX and MATCH may be a more natural fit.

Build a helper row for multiple criteria

Suppose columns B:G contain six records. Row 2 holds the product, row 3 the region, row 4 the month, and row 5 the sales amount. For example, column B might contain Widget, West, Jan, and 125 in those rows. Put a helper row above the data so HLOOKUP has a combined key in the first row of its lookup range.

  1. Enter the product, region, and month you want to find in J1, J2, and J3.
  2. In B1, combine the three fields from the first data column. Fill that formula right through G1.
  3. Use HLOOKUP to search the helper row and return the value from the sales row.

With product, region, month, and sales in B2:G5, and the search criteria in J1:J3, enter this in B1 and fill right to G1: =B2&"|"&B3&"|"&B4. For column B, the result is Widget|West|Jan if those are the three values in B2:B4.

With that helper row in B1:G1, the original fields in B2:G4, sales in B5:G5, and the requested criteria in J1:J3, use: =HLOOKUP($J$1&"|"&$J$2&"|"&$J$3,$B$1:$G$5,5,FALSE). If column B contains Widget, West, Jan, and 125, and J1:J3 contain Widget, West, and Jan, the formula returns 125. The row number 5 points to the fifth row of the selected range, B1:G5—not necessarily worksheet row 5 in every layout.

Why the delimiter and exact match matter

The pipe character in the key is a delimiter: it separates one criterion from the next. Without separators, different combinations can produce the same combined text. For example, joining AB and C gives ABC, as does joining A and BC. Joining the values as AB|C and A|BC keeps those keys distinct.

Choose a separator that does not appear in the source values. If a field can contain a pipe, choose another uncommon character or make the key more robust by adding labels or fixed-width components. Whatever convention you choose, use it in both the helper row and the lookup value.

FALSE tells HLOOKUP to find an exact match. This is important for combined keys: an approximate match could return a result from a different column. Exact matching does not make duplicate keys unique, however. If two columns have the same product, region, and month, HLOOKUP returns the first matching column from left to right.

Use INDEX and MATCH without a helper row

If you prefer not to add a helper row, INDEX and MATCH can test each criteria row separately and find the column where all tests are true. In Microsoft 365 or Excel 2021, you can enter the formula normally. With product in B2:G2, region in B3:G3, month in B4:G4, sales in B5:G5, and the criteria in J1:J3, use: =INDEX($B$5:$G$5,MATCH(1,($B$2:$G$2=$J$1)*($B$3:$G$3=$J$2)*($B$4:$G$4=$J$3),0)). If only column B matches all three criteria and its sales value is 125, the formula returns 125.

Each comparison produces TRUE or FALSE values. Multiplying the comparisons turns TRUE into 1 and FALSE into 0; a column that meets all three tests therefore has a product of 1. MATCH finds that 1, and INDEX returns the sales value at the corresponding position. This formula returns the first qualifying column when several columns match.

In older Excel versions, this kind of calculated array may require confirming the formula with Ctrl+Shift+Enter rather than Enter. Excel displays braces around a legacy array formula, but you should not type those braces yourself. If your version does not handle the formula as expected, use the helper-row method or check the requirements for your Excel release.

Compare HLOOKUP with XLOOKUP

XLOOKUP can also search a horizontal set of combined conditions without a helper row. It is available in Microsoft 365 and Excel 2021 or later. With product, region, and month in B2:G2, B3:G3, and B4:G4; sales in B5:G5; and requested values in J1:J3, use: =XLOOKUP(1,($B$2:$G$2=$J$1)*($B$3:$G$3=$J$2)*($B$4:$G$4=$J$3),$B$5:$G$5,"No match"). If column B is the sole match and its sales amount is 125, the result is 125; if no column matches, the formula displays No match.

Unlike HLOOKUP, XLOOKUP does not need a row index number, and its optional fourth argument can specify what to show when a match is missing. It is a useful alternative when your Excel version supports it. HLOOKUP remains appropriate when you need compatibility with older workbooks or want a formula readers already recognize. INDEX and MATCH is another flexible option, especially when you want the match logic and the returned range to remain separate.

Common errors and how to fix them

  • #N/A: No exact combined key was found. Check spelling, spaces, date values, and whether the helper formula has been filled across every data column.
  • Wrong row returned: HLOOKUP counts rows relative to the start of table_array. If the helper row is at the top of B1:G5, the sales row is number 5 in that range.
  • Unexpected match: Confirm the final argument is FALSE. Also inspect duplicate keys, because the first exact match wins.
  • Keys appear identical but do not match: A cell may contain leading or trailing spaces, or values may have different underlying types. Clean or standardize the source values before looking them up.
  • Dates or numbers form inconsistent keys: Concatenation uses underlying values, not necessarily the cell’s displayed formatting. For dates, use a consistent date representation in both the helper key and lookup key; for numbers, make sure both sides use the same precision and format.

You can display a friendlier message when HLOOKUP finds no match. With the helper row in B1:G1, the full lookup range in B1:G5, sales in row 5 of that range, and criteria in J1:J3, use: =IFNA(HLOOKUP($J$1&"|"&$J$2&"|"&$J$3,$B$1:$G$5,5,FALSE),"No match"). This changes only the #N/A result; it does not conceal other errors, such as an invalid row index.

Make the setup easier to maintain

A helper row is clear and easy to inspect, but it adds a row to the source layout. Keep it with the data, fill it consistently as new columns are added, and make sure the HLOOKUP range expands to include those columns. If you use the workbook with collaborators, label the helper row so its purpose is apparent.

For larger or frequently updated datasets, consider whether the horizontal layout is serving the work well. Storing each record in a row makes filtering, sorting, adding records, and using many lookup formulas easier in many cases. Converting a range to an Excel table can also help manage expanding data. The best choice depends on how the information is maintained and used; a horizontal table is not inherently wrong when it represents a real cross-tab or report.

Before relying on any lookup, decide what should happen if multiple records satisfy the conditions. HLOOKUP, INDEX and MATCH, and the XLOOKUP example above return the first matching column. If duplicates should be combined, such as summing all sales for a product and month, a lookup is the wrong operation; use an aggregation approach instead. If duplicates indicate bad source data, identify and resolve them at the point where that data is maintained.

Frequently Asked Questions

Can HLOOKUP use two criteria?

Yes, if you combine both criteria into a single helper-row key and combine the requested values in the same order. HLOOKUP then searches for that one exact key. Use a separator that prevents different pairs from producing an identical key.

Can I use HLOOKUP with three or more criteria?

Yes. Add each field to the helper key and to the lookup value in the same order. The function still searches for one combined value; the number of components in that value is not limited to the original fields shown in the example.

Why does HLOOKUP return the wrong result when criteria repeat?

HLOOKUP returns the first match from left to right. If more than one column has the same combined key, it cannot decide which record you intended. Check for duplicate combinations or add another field that distinguishes the records.

Do I need a helper row to look up multiple criteria?

No. In Microsoft 365 and Excel 2021, an INDEX and MATCH formula or XLOOKUP can evaluate multiple comparisons directly, as shown above. A helper row is often simpler to audit and can be more compatible with older Excel versions.

Does HLOOKUP search from right to left?

No. HLOOKUP searches across the first row of its selected range and returns from a row beneath it in the matching column. It does not reverse the search direction or return values from above the lookup row.

Related

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.