We can use the Index with Match or Xmatch functions to return a 2D array result in Google Sheets. How is it possible?
Usually, the Index function returns the content of a cell by the given column and row offset. Also, it can return a single column or row.
For example, the formula =index(A2:B,5,2) will return the content from the second column in the fifth row of the range A2:B.
Replace 2 with 0, i.e.,=index(A2:B,5,0), to get the fifth row and 5 with 0, i.e.,=index(A2:B,0,2), to get the second column.
Then, how can we use the Index function to return a 2D array result in Google Sheets?
In spreadsheets, as you may know, we specify a cell range by placing a colon (:) between two cell references.
For example, B2:D10 is a cell range where B2 and D10 are cell references.
Similarly, we can place a colon (:) between two Index results to get a matrix output in Google Sheets.
We can use this feature in several real-life scenarios in Google Sheets.
Examples of Index with Match for a 2D Array in Google Sheets
We will see two to three examples of returning a 2D array using INDEX and MATCH in Google Sheets.
1. Copy a Table up to the Matching Row Header (Field Label)
There are several ways to achieve this task in Google Sheets.
The simplest way is by using Choosecols with Match or Xmatch.
We will come to that later. First, let’s see the Index and Match formula that returns a 2D array result.
=index(B2:B8):index(B2:F8,0,match("Mar",B2:F2,0))
It works like this.
- The formula in the left part of the colon returns the first column in the range.
- The right part formula returns the 4th column (field label matched by the Match function).
It is equal to =index(B2:B8,0,0):index(B2:F8,0,4).
Since we placed the two Index formulas on both sides of the colon, it returns a matrix result.
We can use a CHOOSECOLS + Match alternative formula to copy a table, up to the column, that matches the row header in Google Sheets.
Here is that formula.
=choosecols(B2:F8,sequence(match("Mar",B2:F2,0)))2. Match a Value and Return Additional Rows
In the same table, how to match “b” in the first column and return that row + 2 additional rows?
Solution:
=index(B2:B8,match("b",B2:B8,0)):index(B2:F8,match("b",B2:B8,0)+2,5)The left part of the colon returns “b” because of the row offset # 3 (Match returned). The range to offset is B2:B8.
The right part of the colon uses the same formula, but the row offset is 5 (3+2), and the column offset is also 5.
So the formula returns the range B4:F6.
It’s another example of using Index and Match to return a 2D array result in Google Sheets.
Examples of Index with Xmatch for a 2D Array in Google Sheets
Similar to the Match function, we can use the Xmatch also with Index.
I use the standalone Xmatch mainly in two scenarios.
- When I want to match a key in a column from the last occurrence to the first occurrence.
- Wildcard (partial match).
Below we will use these features of Xmatch and learn how to return a matrix using the Index and Xmatch combo on the go.
1. Return the Rows between the First and Last Occurrences of a Value
I have unstructured sample data that makes it tough to apply functions like FILTER, QUERY, etc.
So I am unable to extract the rows that contain a particular product.
In the following example, I want to extract the rows from “Orange” and “Orange Total.”
The first criterion will be an exact match, and the last one will be a partial (wildcard) match. Because I don’t know if the second keyword is “Orange Total” or “Total of Orange.”
We can use Index with Xmatch for this, and the result will be a 2D Array.
=index(B:B,xmatch("Orange",B:B,0)):index(B:D,xmatch("Orange*",B:B,2,-1),3)
The first XMATCH searches from top to bottom in column B for the keyword “Orange.”
The second Xmatch searches from bottom to top in the same column for the partial match of the keyword “Orange.”
We could return a matrix result using two Index formulas on each side of the colon.
Index with Match or Xmatch for 2D array results will be most useful when you have unstructured data.
