{"id":492,"date":"2025-07-15T13:34:47","date_gmt":"2025-07-15T13:34:47","guid":{"rendered":"https:\/\/functiontranslator.com\/2021\/02\/12\/xmatch\/"},"modified":"2021-02-13T11:37:03","modified_gmt":"2021-02-13T11:37:03","slug":"xmatch","status":"publish","type":"post","link":"https:\/\/functiontranslator.com\/en\/xmatch\/","title":{"rendered":"XMATCH"},"content":{"rendered":"

The XMATCH function is a sophisticated tool designed for locating and retrieving data from a range or an array in both Excel and Google Sheets. This function serves as a contemporary substitute for the traditional MATCH function, offering additional features and greater flexibility. In this article, we will delve into the syntax, examples, and practical uses of XMATCH across both Excel and Google Sheets.<\/p>\n

Syntax<\/h2>\n

The syntax of the XMATCH function is consistent across both Excel and Google Sheets:<\/p>\n

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode], [search_result])<\/code><\/pre>\n
    \n
  • lookup_value<\/code>: The value you are searching for within the lookup_array.<\/li>\n
  • lookup_array<\/code>: The range or array where the search will be conducted.<\/li>\n
  • match_mode<\/code>: (Optional) 0 for an exact match, -1 for exact match or the next smaller item, 1 for exact match or the next larger item. The default is 1.<\/li>\n
  • search_mode<\/code>: (Optional) 1 for normal search from first to last, -1 for reverse search from last to first, 2 for binary search which is suitable for sorted data. The default is 1.<\/li>\n
  • search_result<\/code>: (Optional) 0 returns the position of the matching value, 1 returns the actual value from the matching cell. The default is 0.<\/li>\n<\/ul>\n
    Finding the Position of a Value<\/h2>\n
    For instance, if you have a column (Column A) listing student names and need to find the position of a specific name, you can use the XMATCH function as follows:<\/p>\n\n\n\n
    Name<\/th>\nPosition<\/th>\n<\/tr>\n
    John<\/td>\n=XMATCH(“John”, A:A, 0)<\/td>\n<\/tr>\n<\/table>\n
    Returning the Value<\/h2>\n
    Should you need to retrieve the actual value from the cell, rather than its position, modify the XMATCH function accordingly. For example:<\/p>\n\n\n\n
    Name<\/th>\nResult<\/th>\n<\/tr>\n
    John<\/td>\n=XMATCH(“John”, A:A, 0, , 1)<\/td>\n<\/tr>\n<\/table>\n
    Using XMATCH with Multiple Criteria<\/h2>\n
    XMATCH can also be integrated with other functions to handle searches based on multiple criteria. For example, if you have a list of students along with their scores and want to find a particular student’s score, you can combine XMATCH with the INDEX function:<\/p>\n\n\n\n
    Name<\/th>\nScore<\/th>\n<\/tr>\n
    John<\/td>\n=INDEX(B:B, XMATCH(“John”, A:A, 0))<\/td>\n<\/tr>\n<\/table>\n
    These examples illustrate just a few ways the XMATCH function can be effectively utilized in Excel and Google Sheets for efficient data search and retrieval. Experiment with various parameters and combinations to maximize its utility in your data analysis and management tasks.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Returns the\u00a0relative\u00a0position of an item in an array or range of cells<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[200],"tags":[],"class_list":["post-492","post","type-post","status-publish","format-standard","hentry","category-lookup-and-reference"],"acf":[],"yoast_head":"\nExcel XMATCH Function \u2013 How It Works, Formula Examples and Syntax<\/title>\n<meta name=\"description\" content=\"Learn what the Excel XMATCH function does. Clear syntax explanation and useful formula examples.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/functiontranslator.com\/en\/xmatch\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel XMATCH Function \u2013 How It Works, Formula Examples and Syntax\" \/>\n<meta property=\"og:description\" content=\"Learn what the Excel XMATCH function does. Clear syntax explanation and useful formula examples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/functiontranslator.com\/en\/xmatch\/\" \/>\n<meta property=\"og:site_name\" content=\"Function Translator\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-15T13:34:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-02-13T11:37:03+00:00\" \/>\n<meta name=\"author\" content=\"glavadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:title\" content=\"Excel XMATCH Function \u2013 How It Works, Formula Examples and Syntax\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"glavadmin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/functiontranslator.com\/en\/xmatch\/\",\"url\":\"https:\/\/functiontranslator.com\/en\/xmatch\/\",\"name\":\"Excel XMATCH Function \u2013 How It Works, Formula Examples and Syntax\",\"isPartOf\":{\"@id\":\"https:\/\/functiontranslator.com\/no\/#website\"},\"datePublished\":\"2025-07-15T13:34:47+00:00\",\"dateModified\":\"2021-02-13T11:37:03+00:00\",\"author\":{\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389\"},\"description\":\"Learn what the Excel XMATCH function does. Clear syntax explanation and useful formula examples.\",\"breadcrumb\":{\"@id\":\"https:\/\/functiontranslator.com\/en\/xmatch\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/functiontranslator.com\/en\/xmatch\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/functiontranslator.com\/en\/xmatch\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Function Excel\",\"item\":\"https:\/\/functiontranslator.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Lookup and reference\",\"item\":\"https:\/\/functiontranslator.com\/en\/lookup-and-reference\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"XMATCH\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#website\",\"url\":\"https:\/\/functiontranslator.com\/no\/\",\"name\":\"Function Translator\",\"description\":\"Excel function translator\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/functiontranslator.com\/no\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389\",\"name\":\"glavadmin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g\",\"caption\":\"glavadmin\"},\"sameAs\":[\"https:\/\/functiontranslator.com\"],\"url\":\"https:\/\/functiontranslator.com\/en\/author\/glavadmin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel XMATCH Function \u2013 How It Works, Formula Examples and Syntax","description":"Learn what the Excel XMATCH function does. Clear syntax explanation and useful formula examples.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/functiontranslator.com\/en\/xmatch\/","og_locale":"en_US","og_type":"article","og_title":"Excel XMATCH Function \u2013 How It Works, Formula Examples and Syntax","og_description":"Learn what the Excel XMATCH function does. Clear syntax explanation and useful formula examples.","og_url":"https:\/\/functiontranslator.com\/en\/xmatch\/","og_site_name":"Function Translator","article_published_time":"2025-07-15T13:34:47+00:00","article_modified_time":"2021-02-13T11:37:03+00:00","author":"glavadmin","twitter_card":"summary_large_image","twitter_title":"Excel XMATCH Function \u2013 How It Works, Formula Examples and Syntax","twitter_misc":{"Written by":"glavadmin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/functiontranslator.com\/en\/xmatch\/","url":"https:\/\/functiontranslator.com\/en\/xmatch\/","name":"Excel XMATCH Function \u2013 How It Works, Formula Examples and Syntax","isPartOf":{"@id":"https:\/\/functiontranslator.com\/no\/#website"},"datePublished":"2025-07-15T13:34:47+00:00","dateModified":"2021-02-13T11:37:03+00:00","author":{"@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389"},"description":"Learn what the Excel XMATCH function does. Clear syntax explanation and useful formula examples.","breadcrumb":{"@id":"https:\/\/functiontranslator.com\/en\/xmatch\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/functiontranslator.com\/en\/xmatch\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/functiontranslator.com\/en\/xmatch\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Function Excel","item":"https:\/\/functiontranslator.com\/en\/"},{"@type":"ListItem","position":2,"name":"Lookup and reference","item":"https:\/\/functiontranslator.com\/en\/lookup-and-reference\/"},{"@type":"ListItem","position":3,"name":"XMATCH"}]},{"@type":"WebSite","@id":"https:\/\/functiontranslator.com\/no\/#website","url":"https:\/\/functiontranslator.com\/no\/","name":"Function Translator","description":"Excel function translator","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/functiontranslator.com\/no\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389","name":"glavadmin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g","caption":"glavadmin"},"sameAs":["https:\/\/functiontranslator.com"],"url":"https:\/\/functiontranslator.com\/en\/author\/glavadmin\/"}]}},"_links":{"self":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts\/492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/comments?post=492"}],"version-history":[{"count":3,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts\/492\/revisions"}],"predecessor-version":[{"id":3383,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts\/492\/revisions\/3383"}],"wp:attachment":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/media?parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/categories?post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/tags?post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}