N
Common Ground News

What is match mode in Xlookup?

Author

James Craig

Updated on March 03, 2026

What is match mode in Xlookup?

XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges. match_mode - [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.

Just so, what is Xlookup match mode?

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.

Furthermore, which is faster index match or Xlookup? 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.

Also Know, does Xlookup default to exact match?

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.

Is Xlookup just index match?

So far it looks like XLOOKUP is very similar to INDEX MATCH. The major difference is that we can perform the same lookup in one function (XLOOKUP) instead of two (INDEX MATCH). However, Microsoft gave us some additional features with XLOOKUP that make it even more useful.

Can Xlookup return multiple matches?

XLOOKUP to return multiple columns or rows

One more amazing feature of XLOOKUP is its ability to return more than one value relating to the same match.

What is Xlookup vs VLOOKUP?

The range for the VLOOKUP includes the entire column, but the XLOOKUP splits the referenced ranges to a range to search and one to find the returned value. Also note that the XLOOKUP used one formula to return two values.

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.

Does Xlookup work with text?

Yes. VLOOKUP can search for textual values just as well as it can search for numbers. The example above would search for the product names, which are text rather than numbers.

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?â€.

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.

Is Xlookup new?

Fortunately, the geniuses on the Microsoft Excel team have just released XLOOKUP, a brand-new function available in Office 365* that replaces VLOOKUP. (It also replaces HLOOKUP, the lesser-used function for searching horizontally, in spreadsheet rows.)

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.

Why can't I use Xlookup in Excel?

* Unfortunately, the XLOOKUP function is not backward compatible with earlier versions of Microsoft Excel that only support the VLOOKUP and HLOOKUP functions or compatible with current versions that do not yet include it as one of their lookup functions, such as Excel 2019 and Excel Online.

What does #spill mean in Excel?

#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid.

How do you use Sumif formula?

If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

Can I use Xlookup with two criteria?

XLOOKUP is a new “lookup†type formula. Pre-XLOOKUP, lookups with multiple criteria were complex and not user friendly: now they're simple and easy to do. 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!).

What version of Excel do I need for Xlookup?

As of now, XLOOKUP is only available for the users of Office 365. So, if you're using prior versions of Excel (2010/2013/2016/2019), you won't be able to use this function.

What is binary search in Xlookup?

Just so you know, a binary search is a search algorithm that finds the position of a target value within a sorted array. A binary search compares the target value to the middle element of the array.

What is the best Excel version?

Excel 365 is the latest, greatest and most powerful Excel version you can use and it is available for a very modest monthly subscription.

How do I return multiple values from Xlookup?

Indeed, the XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If you want to return multiple instances match list using formula, we recommend using the INDEX, SMALL and ROW functions. You can change the data range based on your requirement.

Does Xlookup need to be sorted?

XLOOKUP Can Search Last To First

But XLOOKUP can also search last to first and return the last match. We don't need to sort our data in a different order to do this.

How do you use match in Excel?

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

How do I use the Xlookup function in Excel 2019?

INSTALLING THE XLOOKUP ADDIN [GKXLOOKUP]
  1. OPEN EXCEL.
  2. Go to OPTIONS>ADDINS.
  3. Select EXCEL ADD-INS.
  4. Click GO.
  5. A new dialog box will open as shown in the picture containing all the EXCEL ADD-INS list.
  6. We can select the Addins we want to activate.
  7. In our case we want to install the add in , so click BROWSE.

Does Xlookup replace VLOOKUP?

XLOOKUP will be able to replace VLOOKUP, HLOOKUP, and INDEX/MATCH by enabling the selection of two columns (instead of the whole range) and allowing columns to be inserted into the desired data range without needing to change the column numbers.

Is xmatch faster than match?

In summary, the XMATCH function is same as MATCH but more flexible and robust. It can look up both in vertical and horizontal arrays, search first-to-last or last-to-first, find exact, approximate and partial matches, and use a faster binary search algorithm.

Is match faster than VLOOKUP?

With unsorted data, VLOOKUP and INDEX-MATCH have about the same calculation times. With sorted data and an approximate match, INDEX-MATCH is about 30% faster than VLOOKUP. With sorted data and a fast technique to find an exact match, INDEX-MATCH is about 13% faster than VLOOKUP.

Is Getpivotdata faster than VLOOKUP?

So to answer the question. Lookups by getpivotdata are about 10 times as fast as regular index-match lookup, but best performance improvement is achieved by sorting your source data. Sorting your source data could make your lookup 400 times as fast.

What is better than VLOOKUP?

Simply put, the LOOKUP Function is better than VLOOKUP, as it's less restrictive in its use. It was only introduced by Microsoft in 2016, so it's still new to most users. Benefits of LOOKUP vs VLOOKUP: Users can search for data both vertically (columns) and horizontally (rows)

What does VLOOKUP stand for?

VLOOKUP stands for 'Vertical Lookup'. It is a function that makes Excel search for a certain value in a column (the so called 'table array'), in order to return a value from a different column in the same row.

Is Hlookup the same as Xlookup?

XLOOKUP vs HLOOKUP

HLOOKUP requires a row index to specify a result column. XLOOKUP requires a range that contains result values. HLOOKUP performs an approximate match by default. XLOOKUP performs an exact match by default.

Related Documentation

Is it better to refinance or get a home equity line of credit?

How do I create a Gantt chart in asana?

Is Nmn the same as nicotinamide riboside?

What is match mode in Xlookup?