HLOOKUP With Multiple Criteria: Formulas and Examples

HLOOKUP does not accept separate criteria arguments, but you can search by multiple criteria by joining the criteria into one key and looking up that key. For example, combine a product and region in a helper row, then use HLOOKUP with an exact match to return the corresponding value.

What “multiple criteria” means in HLOOKUP

HLOOKUP searches across the first row of a table and returns a value from a specified row beneath the matching column. Its arguments are a single lookup value, a table array, a row number, and an optional match setting. It does not have a built-in way to say “match this product and this region” as two independent conditions.

The usual workaround is to turn the conditions into one combined lookup key. If a product and a region together identify a sales figure, for instance, create keys such as Widget|North and Gadget|South. Then search for the combined key made from the requested product and region. This represents an AND condition: the product and the region must both match in the same column.

This approach suits data arranged horizontally, with records running from left to right. If your records run down rows, HLOOKUP is generally the wrong orientation; VLOOKUP, XLOOKUP, or INDEX and MATCH may fit better.

Set up a small example table

Suppose columns B through G represent six product-region combinations. Put a combined key in row 1, the product names in row 2, the regions in row 3, and sales in row 4:

B: Widget, North, 120
C: Widget, South, 95
D: Gadget, North, 150
E: Gadget, South, 110
F: Widget, East, 80
G: Gadget, East, 125

Enter the product to find in J2 and the region in J3. The combined key row must be the first row of the table array used by HLOOKUP. In this layout, it is created above the source criteria and sales rows, so the return row number for sales will be 4.

Use a helper row for the most compatible formula

With the product names in B2:G2 and regions in B3:G3, enter this formula in B1, then fill it right through G1:

=B2&"|"&B3

That produces keys such as Widget|North and Widget|South. If J2 contains Widget and J3 contains South, use the following formula to return the sales value:

=HLOOKUP(J2&"|"&J3,B1:G4,4,FALSE)

With the example data above, the result is 95, because the matching column has the key Widget|South and its sales value is in row 4. The final argument, FALSE, requests an exact match. It is important for combined keys: an approximate match can return an unintended result, and HLOOKUP’s approximate-match behavior also depends on sorted lookup data.

The helper row is easy to inspect and works in many Excel versions. It also makes the logic visible to other people using the workbook. If you change the criteria rows, make sure the helper formulas still refer to the intended cells.

Build the combined lookup key carefully

A separator such as a vertical bar makes the joined values easier to distinguish than simply joining them with no punctuation. Without a separator, AB plus C and A plus BC would both create ABC. Choose a separator that is not likely to occur in either criterion. If it could occur in your data, use a different separator or a more robust key design; concatenation is only unambiguous when the parts can be reliably distinguished.

Keep the formatting of each criterion consistent in both the helper key and the search value. Numbers stored as text may not behave like numeric values in other kinds of comparisons, and dates can be especially confusing because Excel stores them as serial numbers. If a date is part of the key, convert it to a consistent text representation on both sides using TEXT. For example, with dates in row 3 and the matching search date in J3, a date key could use a format such as yyyy-mm-dd. The format must be the same in the helper and lookup expression.

HLOOKUP is generally not case-sensitive, so keys that differ only by capitalization are not treated as distinct. Also watch for leading or trailing spaces: North and North can produce different joined keys. Cleaning the source data or applying TRIM consistently can prevent apparent “not found” errors.

Create the lookup array directly in Microsoft 365

If you are using Microsoft 365 for Windows, you can avoid a visible helper row by constructing the two-row lookup array inside the formula with HSTACK. Assume product names are in B2:G2, regions in B3:G3, sales in B4:G4, and the requested values in J2 and J3:

=HLOOKUP(J2&"|"&J3,HSTACK(B2:G2&"|"&B3:G3,B4:G4),2,FALSE)

HSTACK places the combined keys in the first row of the generated array and sales in its second row. Therefore, the row index is 2, not 4. For Widget and South, this returns 95. HSTACK is available in Microsoft 365 and Excel 2024; if your Excel version does not recognize it, use the helper-row method instead. The worksheet formula uses commas as argument separators in the usual English-language Excel setup; some regional settings use different separators.

This formula still relies on a combined key and exact matching. HSTACK changes how the temporary table is created, not the underlying logic. A helper row can be preferable when the formula needs to be audited, when other formulas use the same keys, or when workbook compatibility matters.

Understand duplicates, blanks, and missing matches

HLOOKUP returns the first match it encounters from left to right. If two columns have the same combined key, the formula does not report that the data is ambiguous; it simply returns the value from the leftmost matching column. Check that each combination is unique if you expect one result per product and region. If duplicates are meaningful and should be added together, a lookup is not the right operation; use a conditional aggregation formula instead.

If no combined key matches, HLOOKUP returns #N/A. That is often a useful signal that a criterion is misspelled, formatted differently, or absent from the table. You can show a friendlier message by wrapping the lookup in IFNA. With the helper key in B1:G1, sales in B4:G4, and the criteria in J2:J3, use:

=IFNA(HLOOKUP(J2&"|"&J3,B1:G4,4,FALSE),"No match")

For the example query Widget and West, the result is No match, because no such combined key appears in the table. IFNA handles the missing-match error specifically, while IFERROR would also hide other errors that may indicate a formula or range problem. Use an error wrapper only if the message helps the workbook’s readers; it should not conceal a broken setup.

Common problems and practical checks

When a multi-criteria HLOOKUP does not return the expected result, check the structure as well as the criteria. The first row of the table array must contain the keys, and the row index must count from that first row. For the helper-row example, row 1 is the key row and row 4 is sales, so the row index is 4. In the HSTACK example, the constructed array contains only two rows, so the return index is 2.

Confirm the search expression uses the same separator as the helper keys.
Check for extra spaces, inconsistent spelling, and different date or number formats.
Use FALSE for an exact match rather than leaving the match argument out.
Make sure the table range includes the full return row and every column that could match.
Check for duplicate keys if the returned value seems valid but belongs to the wrong record.

One frequent misconception is that HLOOKUP can evaluate several independent tests just because its lookup value contains an ampersand. It cannot: the ampersand joins values into one text key. The method works when that combined key represents the desired combination and is built consistently on both sides.

When another lookup method is a better fit

If the source data is vertical, use a method designed to search down columns rather than turning the table sideways. In Microsoft 365, XLOOKUP can search a calculated set of combined criteria or use Boolean tests, and it lets you specify a custom not-found result. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not available in some older Excel releases.

For a horizontal table with separate criteria rows, INDEX and MATCH can test each row independently without building a concatenated key. Assuming products are in B2:G2, regions in B3:G3, sales in B4:G4, and the requested criteria are in J2 and J3, use:

=INDEX(B4:G4,1,MATCH(1,(B2:G2=J2)*(B3:G3=J3),0))

In current Microsoft 365, this returns 95 for Widget and South. The two comparisons produce arrays of TRUE and FALSE values; multiplying them turns positions where both conditions are true into 1, which MATCH locates. In some older Excel versions, this kind of array calculation may require entering the formula with Ctrl+Shift+Enter, rather than pressing Enter alone. HLOOKUP with a helper row remains a straightforward choice when compatibility and readability are priorities.

Frequently Asked Questions

Can HLOOKUP use two criteria without a helper row?
Yes. In Microsoft 365 or Excel 2024, HSTACK can build an array of combined keys and return values inside the HLOOKUP formula. Otherwise, use a helper row, or choose a different lookup formula that evaluates criteria separately.

Does HLOOKUP with multiple criteria mean both conditions must match?
When you concatenate the criteria into one key, the lookup represents an AND condition: each part of the combined key must match. It does not automatically search for either one criterion or another.

Why does my formula return #N/A even though the values look the same?
Check for trailing spaces, inconsistent date or number formatting, a different separator, and spelling differences. Also verify that the formula is searching the key row and uses FALSE for an exact match.

What happens if two columns have the same combined key?
HLOOKUP returns the first matching column from the left. It does not warn you about duplicates, so verify that the combined criteria uniquely identify a record or use a method that handles multiple results or totals appropriately.

Should I use HLOOKUP or XLOOKUP for multiple criteria?
Use HLOOKUP when the data is horizontal and a helper key makes the table easy to maintain. XLOOKUP or INDEX and MATCH can be more flexible, especially when you want separate criteria tests or your data is vertical. Availability of newer functions depends on your Excel version.

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.