If you are familiar with the MATCH function in Excel then you are going to love XMATCH.&nbsp; Just like the MATCH function XMATCH returns a relative position in a list. Now you are familiar <a href="https://theexcelclub.com/xlookup-in-excel/">XLOOKUP</a>, which replaces the old VLOOKUP function, you know XLOOKUP comes with additional power.&nbsp; This come in the form of new conditions in the formula syntax such as search mode and match types.&nbsp; Well, XMATCH also as this extra power over its predecessor MATCH.

XMATCH is available in Excel/Office 365

The syntax for XMATCH is

XMATCH(Lookup Value, Lookup Array, [Match Mode],[Search Mode])

Where:

<strong>Lookup Value</strong> is the value you are looking to find the relative position

<strong>Lookup Array</strong> is the row or column that contains the Lookup Value

<strong>Match mode</strong> is optional.&nbsp; Unlike the old MATCH function, the default is an exact match.&nbsp; You can also select between
<ol>
 <li>Exact match or next smallest</li>
 <li>Exact match or next largest</li>
 <li>Wildcard match</li>
</ol>
<strong>Search mode</strong> is also optional.&nbsp; The default (and only option in the old MATCH function) is to look from the top down.&nbsp; You can also select last to first and binary searches.&nbsp; If you are working with binary searches. The wildcard match option does not work.
<h3>XMATCH – The Basics</h3>
We have a list of runners sorted by fastest to slowest.&nbsp; We wish to find the position of the selected runner.

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/1_xmatch.png" alt="xmatch in excel" width="594" height="272"><br/>

For this we can use the formula

=XMATCH(E4,B3:B9)

Where E4 contains the lookup value and the lookup array is B3:B9

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/2_xmatch-in-excel.png" alt="xmatch in excel" width="602" height="206"><br/>

From this we can see that Dylan is the 3<sup>rd</sup> fastest.

We can also use the Match mode to search for Approximate matches.&nbsp; XMATCH allows us select between the next smallest or the next largest.

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/3_XMATCH-in-Excel.png" alt="xmatch in excel" width="586" height="256"><br/>

Given a time of 00:30:00 to search for the next smallest we will add -1 as the Match Mode.

=XMATCH(E4,C3:C9,-1)

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/4_XMATCH-in-Excel.png" alt="" width="602" height="201"><br/>

Given a time of 00:30:00 to search for the next largest we will add -1 as the Match Mode.

=XMATCH(E4,C3:C9,1)

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/5_XMATCH-in-Excel.png" alt="xmatch in excel" width="602" height="196"><br/>

The Match mode will also allow you to carry out a wildcard search.&nbsp; To do this, you must set the Match Mode to 2.

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/6_XMATCH-in-Excel.png" alt="xmatch in excel" width="584" height="256"><br/>

Assuming you only have the first two letters of a Name and we want to find the relative position.

We would use the formula

=XMATCH(E4&amp;"*",B3:B9,2)

We must join the text from cell E4 with *.&nbsp; To do this we use &amp;”*”

And we select 2 for the Match mode.

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/7_XMATCH-in-Excel.png" alt="xmatch in excel" width="602" height="199"><br/>
<h3>INDEX and MATCH or INDEX and XMATCH</h3>
The MATCH function is often used with the INDEX function to create a lookup.&nbsp; XMATCH can be used in the same way.&nbsp; Let’s refresh quickly how INDEX and MATCH work together

INDEX returns an intercepting point.&nbsp; Its syntax is

INDEX(Array, row number, [column number])

When using INDEX and MATCH together, MATCH is used to generate the row and column numbers

For example, we want to look up the sales for Lisa in Apr using the INDEX and MATCH functions

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/8_XMATCH-in-Excel.png" alt="xmatch in excel" width="436" height="192"><br/>

To do this we would use the following function

=INDEX(A1:F4,MATCH(A7,A1:A4,0),MATCH(B7,A1:F1,0))

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/9_XMATCH-in-Excel.png" alt="XMACTH in excel" width="602" height="257"><br/>

Using XMATCH we can carry out the same exercise.&nbsp; This time the formula would look as follows

=INDEX(A1:F4,XMATCH(A7,A1:A4),XMATCH(B7,A1:F1))

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/10_XMATCH-in-Excel.png" alt="XMACTH in Excel" width="602" height="193"><br/>

As you can see from the XMATCH, as the default is to search first to last, we do not need to add this to the formula, unlike when we were using MATCH.
<h3>Now it’s your turn – XMATCH in Excel</h3>
Your knowledge of INDEX and MATCH and XLOOKUP can be applied to XMATCH.&nbsp; Can you create a formula using XMATCH to return the following?&nbsp; <a href="https://www.dropbox.com/s/cvj02e2tjrodjzs/xmatch.xlsx?dl=0">You can download the data here</a>

In table 1, column D, pull in the Shipping code from Table 2

In table 2, column I, pull in the last shipment date for each customer.

Share the formula you used for both in the comments below before you watch the video.

<img src="https://theexcelclub.com/wp-content/uploads/2020/08/11_XMATCH-in-Excel.png" alt="XMATCH" width="602" height="473"><br/>

<center>&nbsp;</center></h4>
<center>&nbsp;</center></h3>
<p><center>Do you want to start collecting rewards quickly for learning Excel?&nbsp; Then you should try:</center></p>
<center><a href="https://theexcelclub.com/excel-learn-earn/">10+ Excel Learn and Earn Activities YOU can do Today</a></center></p>

<center><strong>SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX</strong></center></h4>
<a role="button" href="https://theexcelclub.newzenler.com/f/email-signup">SIGN UP</a>https://youtu.be/FfwnsxVL9aw <br /><center><hr/><em>Cross posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : https://theexcelclub.com/how-to-use-xmatch-in-excel/ </em><hr/></center>