{"id":4902,"date":"2025-12-21T20:40:31","date_gmt":"2025-12-21T19:40:31","guid":{"rendered":"https:\/\/sandshine.eu\/?p=4902"},"modified":"2025-12-01T20:48:06","modified_gmt":"2025-12-01T19:48:06","slug":"match-function-in-excel-complete-tutorial-with-examples","status":"publish","type":"post","link":"https:\/\/sandshine.eu\/index.php\/2025\/12\/21\/match-function-in-excel-complete-tutorial-with-examples\/","title":{"rendered":"MATCH Function in Excel: Complete Tutorial with Examples"},"content":{"rendered":"\n<p>Need to find the position of a value in a list? MATCH returns the row or column number where a value appears, making it perfect for lookups, dynamic references, and building flexible formulas. It&#8217;s the ideal partner for INDEX.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">What Makes MATCH Useful<\/h5>\n\n\n\n<p>MATCH locates values precisely:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Find position<\/strong> &#8211; Get the exact row or column number of any value<\/li>\n\n\n\n<li><strong>Flexible matching<\/strong> &#8211; Exact matches, approximate matches, or wildcards<\/li>\n\n\n\n<li><strong>Dynamic formulas<\/strong> &#8211; Build references that adapt automatically<\/li>\n\n\n\n<li><strong>INDEX partner<\/strong> &#8211; The essential companion to INDEX for powerful lookups<\/li>\n\n\n\n<li><strong>Search direction<\/strong> &#8211; Works with both rows and columns<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">The Syntax<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>=MATCH(lookup_value, lookup_array, &#91;match_type])<\/code><\/pre>\n\n\n\n<p>Lookup_value is what you&#8217;re searching for. Lookup_array is where to search. Match_type determines how to match (0 = exact, 1 = less than, -1 = greater than).<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-ce4080d\" data-block-id=\"ce4080d\"><style>.stk-ce4080d {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 1: Basic Position Finding<\/h5>\n\n\n\n<p>Find where &#8220;Keyboard&#8221; appears in the list:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product<\/th><\/tr><\/thead><tbody><tr><td>Laptop<\/td><\/tr><tr><td>Mouse<\/td><\/tr><tr><td>Keyboard<\/td><\/tr><tr><td>Monitor<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=MATCH(\"Keyboard\", A2:A5, 0)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 3<\/p>\n\n\n\n<p>&#8220;Keyboard&#8221; is the 3rd item in the range A2:A5.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-a206d16\" data-block-id=\"a206d16\"><style>.stk-a206d16 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 2: Finding Column Position<\/h5>\n\n\n\n<p>Find which column contains &#8220;Q3&#8221;:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th><\/th><th>Q1<\/th><th>Q2<\/th><th>Q3<\/th><th>Q4<\/th><\/tr><\/thead><tbody><tr><td>Sales<\/td><td>&#8230;<\/td><td>&#8230;<\/td><td>&#8230;<\/td><td>&#8230;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=MATCH(\"Q3\", B1:E1, 0)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 3<\/p>\n\n\n\n<p>&#8220;Q3&#8221; is the 3rd item in the horizontal range.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-5eae611\" data-block-id=\"5eae611\"><style>.stk-5eae611 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 3: Exact Match (Match Type 0)<\/h5>\n\n\n\n<p>Match_type 0 finds exact matches only:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Employee<\/th><th>Department<\/th><\/tr><\/thead><tbody><tr><td>Alice<\/td><td>Sales<\/td><\/tr><tr><td>Bob<\/td><td>IT<\/td><\/tr><tr><td>Carol<\/td><td>Marketing<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=MATCH(\"IT\", B2:B4, 0)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 2<\/p>\n\n\n\n<p>&#8220;IT&#8221; is found at position 2. If not found, returns #N\/A error.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-6f53ba1\" data-block-id=\"6f53ba1\"><style>.stk-6f53ba1 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 4: Approximate Match (Match Type 1)<\/h5>\n\n\n\n<p>Find the highest value that&#8217;s less than or equal to lookup value:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Sales Threshold<\/th><\/tr><\/thead><tbody><tr><td>$0<\/td><\/tr><tr><td>$10,000<\/td><\/tr><tr><td>$25,000<\/td><\/tr><tr><td>$50,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=MATCH(32000, A2:A5, 1)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 3<\/p>\n\n\n\n<p>32,000 falls between $25,000 and $50,000. Returns position 3 ($25,000). List must be sorted ascending.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-935edaf\" data-block-id=\"935edaf\"><style>.stk-935edaf {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 5: Combining with INDEX<\/h5>\n\n\n\n<p>Find the price for a specific product:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product<\/th><th>Price<\/th><\/tr><\/thead><tbody><tr><td>Laptop<\/td><td>$4,500<\/td><\/tr><tr><td>Mouse<\/td><td>$250<\/td><\/tr><tr><td>Keyboard<\/td><td>$800<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=INDEX(B2:B4, MATCH(\"Mouse\", A2:A4, 0))<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: $250<\/p>\n\n\n\n<p>MATCH finds position 2, INDEX retrieves the value at position 2 in the price column.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-b206dc7\" data-block-id=\"b206dc7\"><style>.stk-b206dc7 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 6: Two-Way Lookup with Double MATCH<\/h5>\n\n\n\n<p>Find a specific value in a table:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th><\/th><th>Q1<\/th><th>Q2<\/th><th>Q3<\/th><th>Q4<\/th><\/tr><\/thead><tbody><tr><td>Product A<\/td><td>$125K<\/td><td>$138K<\/td><td>$145K<\/td><td>$162K<\/td><\/tr><tr><td>Product B<\/td><td>$98K<\/td><td>$105K<\/td><td>$112K<\/td><td>$119K<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=INDEX(B2:E3, MATCH(\"Product B\", A2:A3, 0), MATCH(\"Q3\", B1:E1, 0))<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: $112K<\/p>\n\n\n\n<p>First MATCH finds the row, second MATCH finds the column, INDEX retrieves the value.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-e708007\" data-block-id=\"e708007\"><style>.stk-e708007 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Match Type Options<\/h5>\n\n\n\n<figure class=\"wp-block-table is-style-regular\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Match Type<\/th><th>Behavior<\/th><th>List Order Required<\/th><\/tr><\/thead><tbody><tr><td>0<\/td><td>Exact match<\/td><td>No sorting needed<\/td><\/tr><tr><td>1<\/td><td>Largest value \u2264 lookup<\/td><td>Sorted ascending<\/td><\/tr><tr><td>-1<\/td><td>Smallest value \u2265 lookup<\/td><td>Sorted descending<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\">Common Use Cases<\/h5>\n\n\n\n<p><strong>Dynamic Column Reference<\/strong> Find which column contains a header, then use INDEX to get data.<\/p>\n\n\n\n<p><strong>Rank Finding<\/strong> Determine where a value ranks in a sorted list.<\/p>\n\n\n\n<p><strong>Data Validation<\/strong> Check if a value exists before processing.<\/p>\n\n\n\n<p><strong>Flexible Lookups<\/strong> Build formulas that don&#8217;t break when columns move.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">MATCH with Wildcards<\/h5>\n\n\n\n<p>Use wildcards with match_type 0:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Name<\/th><\/tr><\/thead><tbody><tr><td>John Smith<\/td><\/tr><tr><td>Sarah Johnson<\/td><\/tr><tr><td>Mike Williams<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=MATCH(\"*Johnson*\", A2:A4, 0)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 2<\/p>\n\n\n\n<p>Asterisk (*) matches any characters. Question mark (?) matches single character.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">Common MATCH Patterns<\/h5>\n\n\n\n<p><strong>Check if value exists:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=ISNUMBER(MATCH(\"value\", A:A, 0))<\/code><\/pre>\n\n\n\n<p>Returns TRUE if found, FALSE if not.<\/p>\n\n\n\n<p><strong>Find last occurrence:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=MATCH(2, 1\/(A:A=\"value\"), 1)<\/code><\/pre>\n\n\n\n<p>Returns position of last match.<\/p>\n\n\n\n<p><strong>Case-sensitive match:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=MATCH(TRUE, EXACT(A:A, \"Value\"), 0)<\/code><\/pre>\n\n\n\n<p>EXACT is case-sensitive, MATCH finds the TRUE result.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">MATCH vs Similar Functions<\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color\"><thead><tr><th>Function<\/th><th>Purpose<\/th><th>Returns<\/th><\/tr><\/thead><tbody><tr><td>MATCH<\/td><td>Find position<\/td><td>Row or column number<\/td><\/tr><tr><td>SEARCH<\/td><td>Find character position<\/td><td>Character number in text<\/td><\/tr><tr><td>FIND<\/td><td>Find character position (case-sensitive)<\/td><td>Character number<\/td><\/tr><tr><td>VLOOKUP<\/td><td>Find and return value<\/td><td>The value itself<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\">Common Mistakes to Avoid<\/h5>\n\n\n\n<p><strong>Wrong Match Type<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Match_type 1 requires ascending sort<\/li>\n\n\n\n<li>Match_type -1 requires descending sort<\/li>\n\n\n\n<li>If unsorted, use 0 for exact match only<\/li>\n<\/ul>\n\n\n\n<p><strong>Array vs Single Cell<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Lookup_array must be a single row or column<\/li>\n\n\n\n<li>Can&#8217;t search a 2D range directly<\/li>\n\n\n\n<li>Use separate MATCH for row and column<\/li>\n<\/ul>\n\n\n\n<p><strong>Forgetting Error Handling<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MATCH returns #N\/A when value not found<\/li>\n\n\n\n<li>Wrap in IFERROR or IFNA for user-friendly messages<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Error Handling<\/h5>\n\n\n\n<p><strong>Provide default if not found:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=IFERROR(MATCH(\"value\", A:A, 0), \"Not in list\")<\/code><\/pre>\n\n\n\n<p><strong>Check before using:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=IF(ISNUMBER(MATCH(\"value\", A:A, 0)), \"Found\", \"Not found\")<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">Advanced Techniques<\/h5>\n\n\n\n<p><strong>Multiple criteria match:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=MATCH(1, (A:A=\"value1\")*(B:B=\"value2\"), 0)<\/code><\/pre>\n\n\n\n<p>(Array formula &#8211; Ctrl+Shift+Enter in older Excel)<\/p>\n\n\n\n<p><strong>Match closest value:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=MATCH(MIN(ABS(A:A-value)), ABS(A:A-value), 0)<\/code><\/pre>\n\n\n\n<p><strong>Match within range:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=MATCH(TRUE, (A:A>=lower)*(A:A&lt;=upper), 0)<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">Practical Combinations<\/h5>\n\n\n\n<p><strong>Dynamic named range:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=OFFSET(A1, 0, 0, MATCH(\"End\", A:A, 0), 1)<\/code><\/pre>\n\n\n\n<p><strong>Conditional lookup:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=INDEX(C:C, MATCH(1, (A:A=\"criteria1\")*(B:B=\"criteria2\"), 0))<\/code><\/pre>\n\n\n\n<p><strong>Find nth occurrence:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SMALL(IF(A:A=\"value\", ROW(A:A)), n)<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">When to Use MATCH<\/h5>\n\n\n\n<p>Use MATCH when:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You need position numbers, not values<\/li>\n\n\n\n<li>Building INDEX-MATCH lookups<\/li>\n\n\n\n<li>Creating dynamic references<\/li>\n\n\n\n<li>Position matters more than the value<\/li>\n<\/ul>\n\n\n\n<p>Don&#8217;t use MATCH when:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You just need to check if value exists (use COUNTIF)<\/li>\n\n\n\n<li>You want the value itself (use VLOOKUP or INDEX-MATCH)<\/li>\n\n\n\n<li>Searching 2D ranges (use XMATCH or multiple MATCH)<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Start Using It<\/h5>\n\n\n\n<p>Open a spreadsheet with a list. Pick a value to find. Type =MATCH, specify the value and range. See the position number returned.<\/p>\n\n\n\n<p>MATCH becomes powerful when combined with INDEX for flexible lookups.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p><em>Questions about MATCH? Ready to build advanced INDEX-MATCH formulas? Let&#8217;s connect.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>MATCH finds the position of values in ranges, supports wildcards and approximate matching, and powers INDEX-MATCH lookups better than VLOOKUP.<\/p>\n","protected":false},"author":1,"featured_media":4903,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[35,36,37],"tags":[1348,1349,1026,1008,1354,1341,1012,1356,1319,1327,1346,1345,1347,1360,1344,1342,1353,1355,1357,1352,1350,1359,1343,1351],"class_list":["post-4902","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-applications","category-excel","category-learning","tag-advanced-match-formulas","tag-excel-365-match","tag-excel-formulas-tutorial","tag-excel-lookup-functions","tag-excel-match-formula","tag-excel-match-syntax","tag-excel-tutorial-2025","tag-how-to-use-match-in-excel","tag-index-match-excel","tag-index-match-vs-vlookup","tag-learn-match-excel","tag-match-approximate-match","tag-match-function-examples","tag-match-function-explained","tag-match-function-for-beginners","tag-match-function-guide","tag-match-function-in-excel","tag-match-function-tips","tag-match-function-tutorial","tag-match-multiple-criteria","tag-match-position-lookup","tag-match-vs-vlookup","tag-match-wildcards-excel","tag-match-with-index"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"_links":{"self":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4902","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/comments?post=4902"}],"version-history":[{"count":1,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4902\/revisions"}],"predecessor-version":[{"id":4904,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4902\/revisions\/4904"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media\/4903"}],"wp:attachment":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media?parent=4902"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/categories?post=4902"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/tags?post=4902"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}