Google Sheets | XMATCH | INDEX | Function | How To Use Together | Example | Spreadsheet | Tutorial
Use Google Sheets XMATCH and INDEX functions together to extract specific data from a range. While XMATCH returns the position of the specified search key in the specified row or column, INDEX returns the data at the position offset by specified rows and or columns.
-------------------------------------
How to Use XMATCH in Google Sheets?
XMATCH returns the position of the specified search key in a row or column:
• Google Sheets | XMATCH | Function | E...
-------------------------------------
How to Use INDEX in Google Sheets?
Get to know more about INDEX with this step-by-step video tutorial:
• Google Sheets INDEX | Use INDEX Googl...
-------------------------------------
How to Use XLOOKUP for Approximate and Wildcard Match in Google Sheets?
XLOOKUP can return approximate data and supports wildcard search:
• Google Sheets | XLOOKUP | Function | ...
-------------------------------------
How to Use XLOOKUP to Extract Data to Left of Search Key in Google Sheets?
XLOOKUP can extract data to left, right, or left and right of search key:
• Google Sheets | XLOOKUP | Function | ...
-------------------------------------
How to Use XLOOKUP to Extract Multiple Values in Google Sheets?
XLOOKUP allows to extract multiple values from a row or column:
• Google Sheets | XLOOKUP | Function | ...
-------------------------------------
How to Use VLOOKUP in Google Sheets?
Use VLOOKUP to extract a single value to the right of the search key:
• VLOOKUP Google Sheets | How to Use VL...
-------------------------------------
How to Use HLOOKUP in Google Sheets?
Use HLOOKUP for horizontal lookup of search key and extract a single value to its right:
• HLOOKUP Google Sheets | How to Use HL...
----------------------------------------
How to Sum Investment Amount by Quarter?
Sum data of investments, profits, revenues, etc., by quarter:
• Google Sheets | Sum by Quarter | Exam...
-------------------------------------
How to Sum Investment Amount by Month?
The step-by-step tutorial to sum investment amount (or profits, revenues, expenses, orders, etc.) by month:
• Google Sheets | Sum Data like Investm...
-------------------------------------
How to Sum Investment Amount by Week?
The step-by-step tutorial to sum investment amount (or profits, revenues, expenses, orders, etc.) by week:
• Google Sheets | How to Sum Investmen...
-------------------------------------
How to Get Number of Weeks between Dates in Google Sheets?
Here is the link to the step-by-step video tutorial on getting the number of weeks between dates and also the number of fractional days:
• Google Sheets | Get Number of Weeks B...
-------------------------------------
XMATCH Function Formula
=XMATCH(search_key, lookup_range, [match_mode], [search_mode])
For more info on the arguments of XMATCH, please refer to the tutorial titled How to Use XMATCH in Google Sheets? the link to which is given above.
INDEX Function Formula
=INDEX(reference, [row], [column])
To get info on the arguments of INDEX, please refer to the tutorial titled
How to Use INDEX in Google Sheets?
A Practical Application
We'll nest XMATCH inside INDEX to extract specific data in a specific cell
of a range.
First, we'll get the position of a specified product in the product range, using
XMATCH. Let's assume that the position returned by XMATCH is 3.
Extract the data in a cell which is offset by 3 rows and specified number of
columns, using INDEX.
We will use two sheets in the same spreadsheet. Say the first sheet is titled
as product-data and the second as product-price.
Product-data has three columns Product ID, Product, and Price. Product-price has a dropdown menu with its items drawn from the Product column of product-data.
Name the entire data range in product-data, as say product_database. Name the Product column in the same sheet as say product_list.
In the product-price sheet, select a product from the dropdown menu. In the cell immediately to the right of menu, type the following XMATCH-INDEX combo formula.
The XMATCH-INDEX function combo formula to extract data is:
=INDEX(product_database, XMATCH(I3,product_list), 3)
Note that XMATCH is substituted as the value for the row argument and 3 as the value for the column argument of INDEX.
Assume that the value of cell I3 in the above formula is Storage Box. XMATCH will return the position of Storage Box, if found, in the product_list. Let's assume the number returned is 2.
INDEX will offset 2 rows and 3 columns, from the first cell of product_database and returns the data, which is the price of the selected product, in the cell at the intersection of second row and third column. Say the price of Storage Box is Rs. 329.
So the data in product-price sheet will be:
Storage Box Rs. 329
Take a look at this video tutorial, which gives the steps to use the Google Sheets XMATCH and INDEX functions together to extract specific data with examples.