Horizontal XLOOKUP in Google Sheets: Replace HLOOKUP

Some tables grow sideways instead of down: months across the top, one metric per row. Looking up “March’s revenue” in that layout is a horizontal lookup, and the classic tool was HLOOKUP, which shares every weakness of its vertical sibling. XLOOKUP handles the horizontal case with the exact same syntax you already know.

In This Guide

• Row Ranges Instead of Column Ranges
• A Quarterly Table Example
• Replacing HLOOKUP
• Returning a Whole Column of Results
• Mixing Directions: The Two-Way Lookup
• Related Google Sheets Tutorials

Row Ranges Instead of Column Ranges

Nothing in XLOOKUP’s arguments says “column.” The lookup range and result range are just ranges, and their shape steers the search:

• A2:A10 and C2:C10 are one column wide: XLOOKUP searches downward
• A1:D1 and A2:D2 are one row tall: XLOOKUP searches across

The one rule is that both ranges must share the same orientation and size: two rows of four cells each, or two columns of nine cells each. Mismatched shapes fail, the same as in the vertical case.

A Quarterly Table Example

Find Q3’s value:

XLOOKUP searches across row 1, finds Q3 in column C, and returns 300 from the same position in row 2. We verified this in a live sheet: the row-range form returns exactly the value beneath the matched header.

Every other argument works unchanged. A missing_value guards against a quarter that does not exist, match modes allow approximate and wildcard matching across the row, and search_mode -1 searches right-to-left for the last match.

Replacing HLOOKUP

The HLOOKUP version of the same lookup:

The 2 counts rows down from the header, and the FALSE forces exact matching, the same two traps VLOOKUP carries. Insert a row inside the range and the index silently points at the wrong data; forget the FALSE and a missing quarter returns a neighbor.

The XLOOKUP translation drops both:

And one thing HLOOKUP cannot do at all: return from a row above the search row. If your labels sit on row 10 and the data on row 3, =XLOOKUP(key, A10:M10, A3:M3) just works, the horizontal version of the left lookup.

Returning a Whole Column of Results

The same spilling behavior that returns several columns vertically returns several rows horizontally. Widen the result range downward:

With three data rows under the headers, the formula finds the Q2 column and spills its three values downward from the formula cell: one quarter’s entire column of metrics from one formula. The cells below the formula must be empty, or the whole thing returns #REF!, exactly like the horizontal spill case.

Mixing Directions: The Two-Way Lookup

A table with labeled rows AND labeled columns (“revenue by store, by quarter”) needs a two-way lookup: find the row by one key, the column by another. Nest a horizontal XLOOKUP inside a vertical one:

The inner XLOOKUP searches the header row for Q3 and, because its result range spans all the data rows, hands back Q3’s whole column. The outer XLOOKUP then searches the store names and returns Riverside’s row from that column. Two keys, one intersection value.

The same pattern with INDEX and two MATCHes is covered in two-way lookups; the XLOOKUP version reads more naturally once horizontal lookups are familiar.

Related Google Sheets Tutorials

• XLOOKUP: The Complete Guide: The vertical case and all six arguments
• XLOOKUP Return Multiple Columns: Spilling in both directions
• Two-Way Lookup with INDEX MATCH: The classic intersection pattern
• XLOOKUP Match Modes: They work sideways too
• XLOOKUP vs VLOOKUP: The vertical replacement argument