XLOOKUP Horizontal Lookup: How to Use It

XLOOKUP can perform a horizontal lookup by setting the fourth argument (if_not_found is optional, but the key is the fifth argument, match_mode, and the sixth, search_mode—actually, the key is that XLOOKUP’s lookup_array and return_array can be oriented either vertically or horizontally). In plain terms, you point XLOOKUP at a row of lookup values and a row of return values, and it works exactly like VLOOKUP but left-to-right. This article explains how to set it up, common mistakes, and how it compares to HLOOKUP.

What Is a Horizontal Lookup?

A horizontal lookup searches for a value across a row (left to right) and returns a value from another row in the same column. In older Excel, HLOOKUP was the dedicated function for this. XLOOKUP, introduced in Excel 365 and Excel 2021, replaces both HLOOKUP and VLOOKUP because it can search in any direction—vertically, horizontally, or even from bottom-to-top or right-to-left.

The key difference: HLOOKUP requires the lookup row to be the first row of the table, and it can only return values to the right of that row. XLOOKUP has no such restriction—you can return a value from a row above or below the lookup row, and you can search in reverse order if needed.

How XLOOKUP Handles Horizontal Data

The syntax is: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

For a horizontal lookup, both lookup_array and return_array are rows (1×n ranges). For example, if your data has months in row 1 and sales in row 2, you can write:

=XLOOKUP("Mar", A1:L1, A2:L2)

This searches for “Mar” in the range A1:L1 and returns the value from row 2 in the same column. You can also return multiple rows by using a multi-row return array, like A2:L3, which spills the result into two cells.

XLOOKUP vs. HLOOKUP: Key Differences

• Search direction: HLOOKUP only searches left-to-right in the first row. XLOOKUP can search left-to-right or right-to-left (using search_mode = -1).
• Return row position: HLOOKUP can only return values from rows below the lookup row. XLOOKUP can return from any row, above or below.
• If not found: HLOOKUP returns #N/A when not found. XLOOKUP lets you specify a custom message like “Not found”.
• Approximate match: Both support approximate matching, but XLOOKUP gives you explicit control with match_mode (exact, exact or next smaller, exact or next larger, wildcard).
• Spill behavior: XLOOKUP can return an array of values, which spills into adjacent cells automatically.

How to Set Up a Horizontal XLOOKUP (Step-by-Step)

1. Identify the lookup row (the row containing the value you want to find) and the return row (the row with the value you want to retrieve).
2. Click the cell where you want the result.
3. Type =XLOOKUP(.
4. Select the lookup value (e.g., a cell containing the month name).
5. Select the lookup row (e.g., A1:L1).
6. Select the return row (e.g., A2:L2).
7. Optionally add a not-found message: "Not found".
8. Close the parenthesis and press Enter.

Example: =XLOOKUP(H5, A1:L1, A2:L2, "Not found")

Common Mistakes and How to Avoid Them

• Mismatched range sizes: The lookup row and return row must be the same size. If they aren’t, XLOOKUP returns #VALUE!.
• Using vertical ranges by mistake: Ensure both ranges are rows (e.g., A1:L1) not columns (e.g., A1:A12).
• Forgetting the if_not_found argument: Without it, you get #N/A. Add a friendly message to avoid confusion.
• Data type mismatch: If the lookup row contains numbers stored as text, XLOOKUP won’t match a numeric value. Use the VALUE function or ensure consistent formatting.
• Using approximate match accidentally: The default is exact match (0). If you need approximate, set match_mode to -1 or 1 explicitly.

When to Use Horizontal XLOOKUP Instead of HLOOKUP

Use XLOOKUP when:

• You want a custom message when the value isn’t found.
• You need to return a value from a row above the lookup row.
• You want to search right-to-left.
• You’re using Excel 365 or Excel 2021 (older versions don’t support XLOOKUP).

If you’re on an older Excel version, HLOOKUP is still your only option for horizontal lookups. But for most users, XLOOKUP is the better choice because it’s more flexible and easier to read.

Real-World Example: Sales by Month

Imagine you have a table with months in row 1 (Jan-Dec) and sales in row 2. You want to find sales for a month entered in cell H5.

Formula: =XLOOKUP(H5, A1:L1, A2:L2, "Month not found")

If H5 contains “Aug”, the formula returns the sales figure from column H (the 8th column). If the month isn’t in the row, it returns “Month not found”.

Frequently Asked Questions

Can XLOOKUP search horizontally in both directions?

Yes. By default, XLOOKUP searches left-to-right. To search right-to-left, set the search_mode argument to -1. For example: =XLOOKUP("Dec", A1:L1, A2:L2, , 0, -1) will search from L to A.

Does XLOOKUP work in Excel 2019?

No. XLOOKUP is only available in Excel 365 (as of 2020) and Excel 2021. Excel 2019 and earlier do not have it. Use HLOOKUP or INDEX/MATCH instead.

What is the difference between XLOOKUP and INDEX/MATCH for horizontal lookups?

INDEX/MATCH is an older combination that works in all Excel versions. It requires two functions: MATCH finds the position, INDEX returns the value. XLOOKUP does both in one function and is easier to read. For example, INDEX/MATCH horizontal: =INDEX(A2:L2, MATCH(H5, A1:L1, 0)) vs XLOOKUP: =XLOOKUP(H5, A1:L1, A2:L2).

Can XLOOKUP return multiple rows horizontally?

Yes. If you select a multi-row range as the return_array, XLOOKUP will spill the results into adjacent cells. For example, =XLOOKUP("Mar", A1:L1, A2:L3) returns two values: one from row 2 and one from row 3 for March.

Why is my XLOOKUP returning #VALUE! error?

This usually happens when the lookup_array and return_array are not the same size. Check that both ranges have the same number of cells. Also, ensure you’re using rows, not columns, for a horizontal lookup.

How do I do a horizontal lookup with multiple criteria?

XLOOKUP doesn’t directly handle multiple criteria, but you can concatenate criteria in a helper row. For example, if you want to match both month and region, create a row that combines both (e.g., “Jan-East”) and use that as the lookup row. Alternatively, use FILTER or INDEX/MATCH with array formulas.

Related

• Office Tab Feature Demo: What It Does
• How to Merge Two iTunes Libraries
• Document an Endangered Language with Community Consent

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2