If you’re looking for alternatives to the Excel XMATCH function in Google Sheets, you’re in the right place! This post provides alternative formulas to match nearly all XMATCH variations.

Update: The XMATCH function is now available in Google Sheets. Learn more here – XMATCH Function in Google Sheets (Formula Examples).

Although Google Sheets now supports the XMATCH function, alternative formulas can still be useful. For those unfamiliar with XMATCH, it’s a modern lookup function that returns the relative position of a value within a row or column.

In Google Sheets, the MATCH function can serve as an alternative to XMATCH. However, achieving the same functionality may require combining MATCH with other functions. So, learning MATCH alone won’t be enough!

Don’t worry—I’ll guide you through using MATCH as an XMATCH alternative, including some additional functions.

XMATCH Function Alternatives in Google Sheets

In Excel, we commonly use XMATCH for the following purposes:

Take note of the last two columns in the table. When creating XMATCH alternatives in Google Sheets, ensure that the formulas work with both numbers and text strings.

Here are six alternative XMATCH formulas in Google Sheets based on the scenarios in the table above:

Example 1 – Relative Positions in a Numeric Array

Consider the following vertical array in Google Sheets, where we’ll test six XMATCH alternatives.

• Lookup range/array: A2:A10, containing numeric values.
• Note: You do not need to sort the range for these examples.

Below are the formulas in order of the match type and response to duplicates from the table above. Replace “Lookup_Value” with the value you’re matching.

1. Exact match, first occurrence
   • Google Sheets Formula: =MATCH(Lookup_Value, A2:A10, 0)
   • Excel Formula: =XMATCH(Lookup_Value, A2:A10, 0, 1)
2. Exact match, last occurrence
   • Google Sheets Formula: =ArrayFormula(MATCH(1, 1/(A2:A10=Lookup_Value), 1))
   • Excel Formula: =XMATCH(Lookup_Value, A2:A10, 0, -1)

In the following examples, we’ll use the additional functions SORTN and FILTER.

3. Exact match or next largest, first occurrence
   • Google Sheets Formula: =ArrayFormula(MATCH(1, 1/(A2:A10=SORTN(FILTER(A2:A10, A2:A10>=Lookup_Value))), -1))
   • Excel Formula: =XMATCH(Lookup_Value, A2:A10, 1, 1)
4. Exact match or next largest, last occurrence
   • Google Sheets Formula: =ArrayFormula(MATCH(1, 1/(A2:A10=SORTN(FILTER(A2:A10, A2:A10>=Lookup_Value))), 1))
   • Excel Formula: =XMATCH(Lookup_Value, A2:A10, 1, -1)
5. Exact match or next smallest, first occurrence
   • Google Sheets Formula: =ArrayFormula(MATCH(1, 1/(A2:A10=SORTN(FILTER(A2:A10, A2:A10<=Lookup_Value), 1, 0, 1, 0)), -1))
   • Excel Formula: =XMATCH(Lookup_Value, A2:A10, -1, 1)
6. Exact match or next smallest, last occurrence
   • Google Sheets Formula: =ArrayFormula(MATCH(1, 1/(A2:A10=SORTN(FILTER(A2:A10, A2:A10<=Lookup_Value), 1, 0, 1, 0)), 1))
   • Excel Formula: =XMATCH(Lookup_Value, A2:A10, -1, -1)

Example 2 – Relative Positions in a Text Array

The six formulas above work equally well with text arrays. Here’s an example showing their results for a text array. You can use the same lookup range and formulas.

If you’re an Excel or Google Sheets user interested in the original XMATCH formulas, the Excel counterparts provided above will also work in Google Sheets.

That’s it for XMATCH function alternatives in Google Sheets! Thank you for reading. Enjoy exploring these formulas!

Resources

• XMATCH Visible Rows in Google Sheets
• XMATCH First or Last Non-Blank Cell in Google Sheets
• XMATCH Multiple Columns in Google Sheets
• XMATCH vs MATCH in Excel (New vs Old)
• XMATCH Row by Row: Finding Values Across a Range in Google Sheets