N
TruthVerse News

Can Xlookup return multiple values?

Author

Michael Henderson

Updated on February 21, 2026

Can Xlookup return multiple values?

One more amazing feature of XLOOKUP is its ability to return more than one value relating to the same match. All is done with the standard syntax and without any extra manipulations! You enter the formula in the top-left cell of the results range, and Excel automatically spills the results into adjacent blank cells.

Correspondingly, can Xlookup return all matches?

XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate) XLOOKUP can work with vertical and horizontal data. XLOOKUP can perform a reverse search (last to first) XLOOKUP can return entire rows or columns, not just one value.

One may also ask, can you use Xlookup for multiple columns? Note: XLOOKUP can handle arrays natively; there is no need to enter with control + shift + enter. One of the nice advantages of XLOOKUP over VLOOKUP is that XLOOKUP can work with arrays directly, instead of requiring ranges on a worksheet.

XLOOKUP with multiple criteria.

lookup_arrayresult_array
T-shirtLargeBlue16

Thereof, can Xlookup use multiple criteria?

One of the key new features XLOOKUP brings to the table is the ability to lookup using multiple criteria (without complex array formulas – arrays are now dynamic!).

Can xmatch return array?

Notes. XMATCH can work with both vertical and horizontal arrays. XMATCH will return #N/A if the lookup value is not found.

Is Xlookup faster than index match?

We've seen that INDEX/MATCH is much faster than XLOOKUP. The same seems to be true for INDEX/MATCH/MATCH in comparison with a 2D XLOOKUP. INDEX/MATCH/MATCH calculates around 30% faster than a 2D XLOOKUP in our test workbook.

Does Xlookup replace index and match?

On August 28, 2019, Microsoft announced the release of the XLOOKUP Function. The XLOOKUP Function is meant to replace both the VLOOKUP and HLOOKUP Functions. It will also replace the INDEX / MATCH function combo that was previously used to perform more powerful VLOOKUPs.

How do I return multiple values in one cell?

To get multiple lookup values in a single cell, we need to create a function in VBA (similar to the VLOOKUP function) that checks each cell in a column and if the lookup value is found, adds it to the result.

Can Xlookup return an array?

Unlike VLOOKUP, XLOOKUP can return an array with multiple items, so a single formula can return both employee name and department from cells C5:D14.

Is Xlookup case sensitive?

The XLOOKUP function is not case sensitive by default. This means that upper case and lower case characters are equal (for example Japan = JAPAN). You can perform a case sensitive lookup when the list has not been sorted by using the EXACT function.

How do you combine Xlookup And if?

Combine IF Function with VLOOKUP
  1. Select cell E2 by clicking on it.
  2. Assign the formula =IF(VLOOKUP(D2,A2:B6,2,FALSE)>2500,"Yes","No") to cell E2.
  3. Press Enter to apply the formula in cell E2.

Does Xlookup work in Google Sheets?

XLOOKUP IN GOOGLE SHEETS EXPLAINED

XLOOKUP does not (yet!) exist in Google Sheets. XLOOKUP is a new, modern replacement for older lookup functions in Excel, such as VLOOKUP and INDEX MATCH, but it doesn't exist in Google Sheets in the same way other lookup functions do.

How do you use the wildcard in Xlookup?

Wildcard searches in XLOOKUP are also possible.

Advanced XLOOKUP example 2: Wildcard lookups.

Wildcard characterExplanation
~ (tilde) followed by ?, *, or ~You actually want to search for the asterisk or question mark? Put a tilde in front. A question mark, asterisk, or tilde For example, fy06~? finds “fy06?â€.

How do I return blank instead of zero?

Use the IF function to do this. Use a formula like this to return a blank cell when the value is zero: =IF(A2-A3=0,â€â€,A2-A3)

How do you use index match for multiple results?

We use INDEX MATCH with multiple criteria by following these 5 steps:
  1. Step 1: Understanding the foundation.
  2. Step 2: Insert a normal MATCH INDEX formula.
  3. Step 3: Change the lookup value to 1.
  4. Step 4: Enter the criteria.
  5. Step 5: Ctrl + Shift + Enter.

How do you use concatenate?

There are two ways to do this:
  1. Add double quotation marks with a space between them " ". For example: =CONCATENATE("Hello", " ", "World!").
  2. Add a space after the Text argument. For example: =CONCATENATE("Hello ", "World!"). The string "Hello " has an extra space added.

Can Xlookup look left?

What is XLOOKUP? The new XLOOKUP function has solutions for some of the biggest limitations of VLOOKUP. Plus, it also replaces HLOOKUP. For example, XLOOKUP can look to its left, defaults to an exact match, and allows you to specify a range of cells instead of a column number.

Related Documentation

What is the most common ethnic group in the Middle East?

What are TOU rates?

What was the role of Griffiss Air Force Base in the events of 9 11?

Can Xlookup return multiple values?