XLOOKUP to Search Across Columns in Google Sheets

If you want to search for a key across multiple columns and return a value from the matched row, Google Sheets’ XLOOKUP can handle it — even across a matrix or 2D range.

We’ve already covered a similar technique using VLOOKUP. But XLOOKUP brings an extra advantage: if your search key appears in multiple rows, you can choose to return the result from either the first or the last matching row — meaning it can search from top to bottom or bottom to top.

You might think the ability to lookup left is another advantage of XLOOKUP, but in Google Sheets, that’s not a major issue with VLOOKUP, since you can rearrange or virtually stack ranges to get around that.

Let’s now look at how to search across multiple columns with XLOOKUP in Google Sheets, using a realistic project assignment example.

Table of Contents

Introduction

The XLOOKUP function typically searches for a key in one column and returns the corresponding value from another column. For example:

=XLOOKUP("Water Treatment", A2:A, B2:D)

This searches column A for the project name “Water Treatment” and returns the team members listed in columns B to D.

But what if you want to reverse that logic — i.e., search for a team member across columns B to D, and return the project name from column A?

That’s the use case we’ll solve in this post — searching a 2D range with XLOOKUP in Google Sheets.

Sample Data – Realistic Project Assignment Example

Imagine a refinery construction tracker where each row represents a project and the three columns represent different team assignments:

Sample project assignment table with employee IDs across Team A, B, and C columns for XLOOKUP demonstration in Google Sheets

Let’s say you want to find which project employee ID 1013 is assigned to. The correct answer is “Pipeline Fabrication” — but since the employee could appear in any team column, we need to search across multiple columns with XLOOKUP.

You might even have a list of employee IDs and want to find the project for each of them — no problem, the formula below can handle that too.

The XLOOKUP Formula to Search Multiple Columns

Here’s the formula that allows XLOOKUP to search across multiple columns (a 2D range) and return the associated value from another column — all without using resource-heavy LAMBDA functions:

=ArrayFormula(
   LET(
      search_key, F2:F, 
      lookup_range, B2:D, 
      result_range, A2:A, 
      blank, IFNA(WRAPCOLS(,ROWS(result_range))), 
      m_lookup_range, TRANSPOSE(QUERY(TRANSPOSE(HSTACK(blank, lookup_range, blank)),,9^9)), 
      xl, IF(search_key<>"", XLOOKUP("* "&search_key&" *", m_lookup_range, result_range,,2, 1),), 
      xl
   )
)

Replace These Ranges as Needed:

F2:F: Your search key(s) — e.g., employee IDs
B2:D: The 2D range across which you want to search
A2:A: The result column (e.g., project names)

This formula allows you to search for a single value or multiple values without needing to drag the formula down. It auto-expands.

XLOOKUP formula returning project names based on employee IDs searched across multiple team columns in Google Sheets

And here’s the best part — to search from bottom to top, just change the last 1 to -1:

..., result_range,,2, -1)

Key Features of This Formula

Simple syntax — only need to specify search key, lookup range, and result range
Search direction control — top-to-bottom or bottom-to-top
High performance — doesn’t use LAMBDA, so it works even with large datasets

Formula Breakdown

Let’s break it down step-by-step:

1. `search_key`

search_key, F2:F

This is the cell or range containing the search key(s), such as employee IDs.

2. `lookup_range`

lookup_range, B2:D

This is your 2D matrix where the search happens — e.g., Team A to Team C columns.

3. `result_range`

result_range, A2:A

This is the column from which to return the result — in our example, Project Names.

4. `blank`

blank, IFNA(WRAPCOLS(,ROWS(result_range)))

This creates a truly blank column (not "") that matches the number of rows in result_range. It’s used to pad the left and right of the lookup range when combining columns into a single list — helping ensure clean spacing for wildcard matching later.

How it works:

WRAPCOLS(, ROWS(result_range)) with no input returns one empty cell followed by #N/As.
Wrapping it with IFNA turns all those #N/A values into true blank cells.
The result is a vertical column of empty cells used to pad the lookup matrix.

5. `m_lookup_range`

m_lookup_range, TRANSPOSE(QUERY(TRANSPOSE(HSTACK(blank, lookup_range, blank)),,9^9))

This combines the 2D lookup range into a single-column array, where each cell contains the values of a single row — separated by spaces. For instance, the row B2:D2, which contains the values 1002, 1005, and 1010, would be transformed into the text string "1002 1005 1010".

It’s based on a column-stacking technique I previously explained in:

👉 A Flexible Array Formula for Joining Columns in Google Sheets

What’s different here?
We add truly blank columns on both sides using HSTACK(blank, lookup_range, blank). This creates spacing at the start and end of each row string, making it easier to match whole values using wildcards.

For example:

Without padding:
"1002 1005 1010"
With padding:
" 1002 1005 1010 "

This padded, flattened structure enables reliable wildcard searches across all team columns.

6. `xl` – The Final XLOOKUP

xl, IF(search_key<>"", XLOOKUP("* "&search_key&" *", m_lookup_range, result_range,,2, 1),)

This is where the search happens:

"* "&search_key&" *" adds wildcards on both sides and a space before and after the key for exact match detection in the flattened array.
The last argument 1 ensures the search happens from top to bottom (use -1 for bottom to top).

Tips for Using XLOOKUP Across Multiple Columns in Google Sheets

Here are a couple of important things to consider when using this formula to search a 2D range with XLOOKUP:

1. Use a Single-Column Result Range

When using multiple search keys, the result range must be a single column.
This isn’t specific to the customized formula in this tutorial — it’s simply how XLOOKUP behaves in Google Sheets. If you’re only using a single search key, you can return results from multiple columns without any issue.

2. Ensure Consistent Date/Time Formatting

Since this formula flattens the lookup range into text-based rows, any dates or times are treated as text strings.
If the original values are inconsistently formatted — for example:

"20/12/2024 22/12/2024 03/01/2025" ✅ (consistent)
"20/12/2024 22/12/24 3/1/2025" ❌ (inconsistent)

…the wildcard match may fail, even if the logical date values are the same. To avoid issues, make sure all date or time values in the lookup range use a uniform, unambiguous format (like dd/mm/yyyy) so they match your search keys as expected.

When to Use VLOOKUP Instead (Top-Down Only)

If you’re only doing a top-down search and want to return entire rows or multiple columns, VLOOKUP might be more convenient — especially since it allows a 2D result range.

Check out my tutorial here:
👉 VLOOKUP to Search Across Multiple Columns in Google Sheets

Use This XLOOKUP Sheet

How to Search Across Multiple Columns with XLOOKUP in Google Sheets

Introduction

Sample Data – Realistic Project Assignment Example

The XLOOKUP Formula to Search Multiple Columns

Key Features of This Formula

Formula Breakdown

1. search_key

2. lookup_range

3. result_range

4. blank

5. m_lookup_range

6. xl – The Final XLOOKUP