Video Discription |
The Google Sheets XMATCH function assists to get the position of an item in the specified range, which is known as the lookup range. The position returned by XMATCH can then be used to extract data corresponding to the position, using a function like INDEX.
XMATCH is more powerful and versatile than the MATCH function. For instance, XMATCH allows to use wildcard characters in the search key, while MATCH does not.
-------------------------------------
How to Use XLOOKUP for Approximate and Wildcard Match in Google Sheets?
XLOOKUP can return approximate data and supports wildcard search:
https://youtu.be/GJXf_Bx2C5M
-------------------------------------
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:
https://youtu.be/6jslCDy893o
-------------------------------------
How to Use XLOOKUP to Extract Multiple Values in Google Sheets?
XLOOKUP allows to extract multiple values from a row or column:
https://youtu.be/oYsXgdzuAkg
-------------------------------------
How to Use VLOOKUP in Google Sheets?
Use VLOOKUP to extract a single value to the right of the search key:
https://youtu.be/_yKn70cl-Mo
-------------------------------------
How to Use HLOOKUP in Google Sheets?
Use HLOOKUP for horizontal lookup of search key and extract a single value to its right:
https://youtu.be/nj4Q84RkNEg
----------------------------------------
How to Sum Investment Amount by Quarter?
Sum data of investments, profits, revenues, etc., by quarter:
https://youtu.be/Lw3IMO2oN20
-------------------------------------
How to Sum Investment Amount by Month?
The step-by-step tutorial to sum investment amount (or profits, revenues, expenses, orders, etc.) by month:
https://youtu.be/kVu682-6Dlo
-------------------------------------
How to Sum Investment Amount by Week?
The step-by-step tutorial to sum investment amount (or profits, revenues, expenses, orders, etc.) by week:
https://youtu.be/QmqCBR5tdUk
-------------------------------------
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:
https://youtu.be/Yc5U2Pf99iw
-------------------------------------
How to Use ROUND, ROUNDUP, and ROUNDDOWN in Google Sheets?
Get to know more about these three functions with this step-by-step video tutorial:
https://youtu.be/D6QwxahwI7E
-------------------------------------
XMATCH Function Formula
=XMATCH(search_key, lookup_range, [match_mode], [search_mode])
The equal-to symbol indicates that what follows it is a formula.
XMATCH is the name of the function.
search_key is the data to search.
lookup_range is the range in which to search for the search key.
match_mode is optional, and its value specifies how search key should be
matched with the data in the lookup range.
The values of match_mode are 0 (default), for an exact match; 1 for an exact match or a value higher than the search key; -1 for an exact match or a value lower than the search key; 2, if there are wildcard character(s) in the search key.
search_mode is optional, and its value specifies the direction of search or the type of search.
The values of search mode are 1 (default) for searching from first to last cell; -1 for searching from last to first cell, 2 for binary search. The data should be sorted in ascending order; -2 for binary search. The data should be sorted in descending order.
Examples of XMATCH Function Formula
Example 1
Assume that cells A3 to A7 have candidate IDs. Say E2 has the header Search Candidate ID and cell E3 has the value 10366.
Assume that you want to get the position of the above value in the range A3 to A7.
The XMATCH function formula without using any optional attributes is:
=XMATCH(E3, A3:A7)
Let's assume that candidate ID 10366 is in cell A5 of the range A3 to A7. XMATCH returns 3.
Example 2
As in Example 1, cells A3 to A7 have candidate IDs. Assume that you want to get the position of a candidate ID which starts with 5 and the remaining numbers can be any and all. For this task, use the wildcard character *.
Assume that in cell E3, you want to type the search key. In this cell, type 5*.
The XMATCH function formula for wildcard search is:
=XMATCH(E3, A3:A7, 2)
Assume that the first ID that starts with 5 is in cell A3 of the range A3 to A7.
XMATCH returns 1.
Review this video tutorial, which gives the steps to use the Google Sheets XMATCH function with examples. iEhCiWhkMq0 |