XLOOKUP Lookup Row or Column? Straight Answer

XLOOKUP can search both rows and columns, but its default and most common use is a vertical lookup, searching down the first column of a range and returning a value from the same row in another column. To search across a row instead, you set the optional search_mode argument to 2 (binary search) or 1 (exact match, from first to last) — but the real trick is that XLOOKUP always works on a one-dimensional lookup array, so you choose the orientation by how you define that array.

How XLOOKUP Actually Works

XLOOKUP takes four arguments, with two optional ones:

• lookup_value – what you are searching for.
• lookup_array – the row or column you search in.
• return_array – the row or column you pull the result from.
• if_not_found – optional text if no match (e.g., “Not found”).
• match_mode – optional; 0 for exact match (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard.
• search_mode – optional; 1 for first-to-last (default), -1 for last-to-first, 2 for binary search (requires sorted data), -2 for binary search (reverse sorted).

Notice that the lookup and return arrays are separate. They do not have to be adjacent, and they do not have to be the same size in both dimensions — they only need to have the same number of rows (for vertical) or the same number of columns (for horizontal).

Vertical Lookup (Column Search) – The Default

When you write =XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C), you are telling Excel to look down column A for the value in A2, and once found, return the value from column C in the same row. This is the classic “find a product ID, get its price” pattern. The lookup array is a column (A:A), and the return array is another column (C:C). Because both are vertical, XLOOKUP naturally returns a vertical result.

This is what most people mean when they ask “does XLOOKUP search rows or columns?” – the answer is: it searches whatever orientation you give it, but the default mental model is vertical.

Horizontal Lookup (Row Search) – How to Do It

To search across a row instead of down a column, you simply define the lookup array as a row range. For example:

=XLOOKUP(B2, A1:F1, A2:F2)

Here, the lookup array is the row A1:F1, and the return array is the row A2:F2. Excel will scan across the first row for the value in B2, and when it finds it, it returns the value from the second row in the same column. This is a horizontal lookup, and it works exactly like the old HLOOKUP function.

You can also use an entire row reference like 1:1 and 2:2, but be careful – that searches the entire row, which can be slow if your sheet has many columns. It is better to limit the range to the actual data area.

When to Use Row vs. Column Lookup

Choose vertical lookup when your data is organized in columns – for example, a product list where each row is a product and columns hold attributes like price, stock, and category. Choose horizontal lookup when your data is organized in rows – for example, a timeline where each column is a date and rows hold sales, inventory, or temperature readings.

Many real-world tables are a mix. You might have a matrix where the top row contains months and the left column contains product names. To find the sales of “Widget” in “March”, you would do two lookups: first a horizontal lookup to find the column for March, then a vertical lookup to find the row for Widget. XLOOKUP can even do this in one formula using nested XLOOKUPs, but that is an advanced technique.

Common Mistakes and Misconceptions

The most common mistake is assuming XLOOKUP can search a two-dimensional range like a table. It cannot – the lookup array must be a single row or a single column. If you give it a multi-column range, it will return a #VALUE! error or an incorrect result.

Another mistake is forgetting that the return array must be the same size as the lookup array. If you search a column of 100 rows but return a column of only 50 rows, you get a #VALUE! error.

People also confuse match_mode with search_mode. The match mode controls what counts as a match (exact, wildcard, approximate), while the search mode controls the direction of the search (first-to-last, last-to-first, binary). For a simple horizontal lookup, you usually leave both at their defaults.

A common misconception is that XLOOKUP is always faster than VLOOKUP. In most cases it is, but if you use a binary search (search_mode 2 or -2) on unsorted data, you will get wrong results. Binary search requires the lookup array to be sorted in ascending (or descending) order. If you are not sure your data is sorted, stick with the default search mode 1.

XLOOKUP vs. VLOOKUP and HLOOKUP

XLOOKUP replaces both VLOOKUP and HLOOKUP. The old functions forced you to choose a vertical or horizontal approach, and VLOOKUP had the limitation that the return column had to be to the right of the lookup column. XLOOKUP has no such restriction – you can return a column to the left, and you can search in any direction.

Another advantage: XLOOKUP does not require the lookup array to be the first column of a table. You can search in column B and return from column A, which was impossible with VLOOKUP without rearranging your data.

For approximate matches, XLOOKUP with match_mode -1 or 1 works like VLOOKUP’s TRUE, but it is more flexible because you can specify whether to take the next smaller or next larger value. This is useful for tax brackets, shipping weight tiers, or discount tables.

Practical Examples

Let’s say you have a table with months in row 1 (Jan, Feb, Mar…) and products in column A (Widget, Gadget, Gizmo). To find the sales of Gadget in March, you could use:

=XLOOKUP("Mar", A1:F1, XLOOKUP("Gadget", A2:A10, B2:F10))

This works because the inner XLOOKUP returns the entire row for Gadget (B2:F10), and the outer XLOOKUP finds the March column in that row. However, this requires that the inner lookup returns a row that is the same size as the outer lookup array. It is a neat trick but can be confusing; a simpler approach is to use INDEX and MATCH, but XLOOKUP is more readable.

Another example: you have a list of employee IDs in row 1 and their names in row 2. To find the name for ID 104, use =XLOOKUP(104, A1:F1, A2:F2). This is a horizontal lookup.

Frequently Asked Questions

Can XLOOKUP search both a row and a column at the same time?

No, not directly. The lookup_array must be a single row or a single column. To search a two-dimensional table, you need to nest two XLOOKUPs or use INDEX/MATCH.

Does XLOOKUP work like VLOOKUP but better?

Yes, for vertical lookups it is a drop-in replacement, but it also works horizontally and can return values to the left of the lookup column. It also handles errors more gracefully with the if_not_found argument.

Why is my XLOOKUP returning #VALUE! when I use a row?

Check that the lookup_array and return_array have the same number of cells. If you use entire rows (1:1 and 2:2), they are the same size, but if you use partial ranges like A1:F1 and A2:E2, they are not – that causes an error.

What is the difference between match_mode and search_mode?

match_mode tells XLOOKUP what counts as a match (exact, wildcard, approximate). search_mode tells it the order to scan the array (first to last, last to first, or binary). They are independent.

Can I use XLOOKUP to return multiple values?

Yes, if you use a dynamic array formula. For example, =XLOOKUP(A2, A:A, B:B) returns one value, but if you wrap it in something like FILTER or use a return array that is multiple columns, it will spill. However, the basic XLOOKUP returns a single value.

Is XLOOKUP available in all Excel versions?

XLOOKUP is available in Excel 2021 and Microsoft 365. It is not in Excel 2019 or earlier. If you are using an older version, you will need to use VLOOKUP, HLOOKUP, or INDEX/MATCH.

Related

• Slack Tips and Tricks for Beginners
• Hardware Support Services: What They Cover
• Unlock All Three Moonlight Peaks Forms