{"id":17207,"date":"2025-01-18T11:09:06","date_gmt":"2025-01-18T02:09:06","guid":{"rendered":"https:\/\/84office.jp\/?p=17207"},"modified":"2025-01-18T11:09:07","modified_gmt":"2025-01-18T02:09:07","slug":"googlesheets-xmatch","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/googlesheets-xmatch\/","title":{"rendered":"An Overview and How to Use the XMATCH Function in Google Sheets"},"content":{"rendered":"<div class=\"codoc-evacuations\" style=\"display:none;\" data-shortcode=\"\"><\/div>\n<p>Google Sheets offers several convenient functions for searching data and retrieving its position.<\/p>\n\n\n\n<p>Among them, the <strong>XMATCH function<\/strong> is a new and improved version of the traditional <strong>MATCH function<\/strong>.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-40\">In this article, we will explore the differences between XMATCH and MATCH functions, as well as the basic usage of XMATCH.<\/p>\n\n\n<div class=\"swell-block-postLink u-mb-ctrl u-mb-60\">\t\t\t<div class=\"p-blogCard -external\" data-type=\"type3\" data-onclick=\"clickLink\">\n\t\t\t\t<div class=\"p-blogCard__inner\">\n\t\t\t\t\t<span class=\"p-blogCard__caption\">\u5408\u540c\u4f1a\u793e\u6a4b\u722a\u30aa\u30d5\u30a3\u30b9<\/span>\n\t\t\t\t\t<div class=\"p-blogCard__thumb c-postThumb\"><figure class=\"c-postThumb__figure\"><img decoding=\"async\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/a98b1120712ae2558c294773114e59b3-2.png\" alt=\"\" class=\"c-postThumb__img u-obf-cover\" width=\"320\" height=\"180\"><\/figure><\/div>\t\t\t\t\t<div class=\"p-blogCard__body\">\n\t\t\t\t\t\t<a class=\"p-blogCard__title\" href=\"https:\/\/84office.jp\/en\/googlesheets-xlookup\/\">Master XLOOKUP: Simplify Data Search and Learn the Key Differences from VLOOKUP and HLOOKUP | \u5408\u540c\u4f1a&#8230;<\/a>\n\t\t\t\t\t\t<span class=\"p-blogCard__excerpt\">Have you ever found yourself struggling to search for data in Google Sheets, especially when using traditional functions<\/span>\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/div>\n\n\n<h2 class=\"wp-block-heading\">Comparison of XMATCH and MATCH Functions<\/h2>\n\n\n\n<figure class=\"wp-block-table u-mb-ctrl u-mb-40 is-all-centered--va\"><table class=\"has-fixed-layout\"><thead style=\"--thead-color--bg:var(--color_pale03);--thead-color--txt:var(--swl-text_color--black)\"><tr><th class=\"has-text-align-left\" data-align=\"left\"><strong>Feature<\/strong><\/th><th class=\"has-text-align-left\" data-align=\"left\">XMATCH<\/th><th class=\"has-text-align-left\" data-align=\"left\">MATCH<\/th><\/tr><\/thead><tbody><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong><strong>Search Direction<\/strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" data-has-cell-bg=\"1\" data-has-cell-icon=\"m-obj\"><span data-icon=\"doubleCircle\" data-icon-size=\"m\" data-icon-type=\"obj\" aria-hidden=\"true\" class=\"swl-cell-bg\">\u00a0<\/span>Can specify both vertical and horizontal search<\/td><td class=\"has-text-align-left\" data-align=\"left\" data-has-cell-bg=\"1\" data-has-cell-icon=\"m-obj\"><span data-icon=\"triangle\" data-icon-size=\"m\" data-icon-type=\"obj\" aria-hidden=\"true\" class=\"swl-cell-bg\">\u00a0<\/span> Vertical search only<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong><strong>Match Types<\/strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" data-has-cell-bg=\"1\" data-has-cell-icon=\"m-obj\"><span data-icon=\"doubleCircle\" data-icon-size=\"m\" data-icon-type=\"obj\" aria-hidden=\"true\" class=\"swl-cell-bg\">\u00a0<\/span>Supports exact match, approximate match, and partial match<\/td><td class=\"has-text-align-left\" data-align=\"left\" data-has-cell-bg=\"1\" data-has-cell-icon=\"m-obj\"><span data-icon=\"triangle\" data-icon-size=\"m\" data-icon-type=\"obj\" aria-hidden=\"true\" class=\"swl-cell-bg\">\u00a0<\/span>Supports exact match and approximate match<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong><strong>Error Handling<\/strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" data-has-cell-bg=\"1\" data-has-cell-icon=\"m-obj\"><span data-icon=\"doubleCircle\" data-icon-size=\"m\" data-icon-type=\"obj\" aria-hidden=\"true\" class=\"swl-cell-bg\">\u00a0<\/span> Allows custom return values if no match is found<\/td><td class=\"has-text-align-left\" data-align=\"left\" data-has-cell-bg=\"1\" data-has-cell-icon=\"m-obj\"><span data-icon=\"triangle\" data-icon-size=\"m\" data-icon-type=\"obj\" aria-hidden=\"true\" class=\"swl-cell-bg\">\u00a0<\/span>Requires functions like IFERROR to handle errors<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong><strong>Flexible Use Cases<\/strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" data-has-cell-bg=\"1\" data-has-cell-icon=\"m-obj\"><span data-icon=\"doubleCircle\" data-icon-size=\"m\" data-icon-type=\"obj\" aria-hidden=\"true\" class=\"swl-cell-bg\">\u00a0<\/span> Suitable for both simple and complex searches<\/td><td class=\"has-text-align-left\" data-align=\"left\" data-has-cell-bg=\"1\" data-has-cell-icon=\"m-obj\"><span data-icon=\"circle\" data-icon-size=\"m\" data-icon-type=\"obj\" aria-hidden=\"true\" class=\"swl-cell-bg\">\u00a0<\/span>Best suited for simple searches<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<ul class=\"wp-block-list has-border -border02 u-mb-ctrl u-mb-40 has-swl-pale-03-background-color has-background\">\n<li><strong><strong>The XMATCH function supports both vertical and horizontal searches.<\/strong><\/strong><\/li>\n\n\n\n<li><strong><strong>It also allows partial match searches.<\/strong><\/strong><\/li>\n<\/ul>\n\n\n\n<p>Both the <strong>XMATCH<\/strong> and <strong>MATCH<\/strong> functions are designed to search for a specified value and return its position.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-40\">However, XMATCH offers greater flexibility and enhanced functionality.<\/p>\n\n\n\n<dl class=\"swell-block-dl u-mb-ctrl u-mb-60\">\n<dt class=\"swell-block-dl__dt\">Specify Search Order (Ascending or Descending)<\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<p>With the XMATCH function, you can specify the search order as either ascending or descending.<br>This feature enables efficient data searches regardless of how the list is sorted.<\/p>\n<\/dd>\n\n\n\n<dt class=\"swell-block-dl__dt\">Flexible Searches with Partial Matches:<\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<p>XMATCH allows partial matches, enabling you to perform approximate searches or find data that matches only part of a string.<br>This is especially useful when multiple possibilities exist, or when you don\u2019t know the exact value you\u2019re searching for.<\/p>\n<\/dd>\n<\/dl>\n\n\n\n<h2 class=\"wp-block-heading\">XMATCH Syntax and Basic Usage<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>XMATCH Syntax<\/strong><\/h3>\n\n\n\n<p>The basic syntax of the <strong>XMATCH<\/strong> function is as follows:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])\" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F92672\">=<\/span><span style=\"color: #A6E22E\">XMATCH<\/span><span style=\"color: #F8F8F2\">(lookup_value, lookup_array, [match_mode], [search_mode])<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><code>lookup_value<\/code><\/strong>: The value you want to search for.<\/li>\n\n\n\n<li><strong><code>lookup_array<\/code><\/strong>: The range where the search will be performed.<\/li>\n\n\n\n<li><strong><code>[match_mode]<\/code><\/strong> <em>(optional)<\/em>: Specifies the type of match (exact match, approximate match, etc.).<\/li>\n\n\n\n<li class=\"u-mb-ctrl u-mb-40\"><strong><code>[search_mode]<\/code><\/strong> <em>(optional)<\/em>: Allows you to set the search order, either top-to-bottom or bottom-to-top.<\/li>\n<\/ul>\n\n\n\n<div class=\"swell-block-accordion\">\n<details class=\"swell-block-accordion__item\" data-swl-acc=\"wrapper\"><summary class=\"swell-block-accordion__title\" data-swl-acc=\"header\"><span class=\"swell-block-accordion__label\">Match Types and Search Modes<\/span><span class=\"swell-block-accordion__icon c-switchIconBtn\" data-swl-acc=\"icon\" aria-hidden=\"true\" data-opened=\"false\"><i class=\"__icon--closed icon-caret-down\"><\/i><i class=\"__icon--opened icon-caret-up\"><\/i><\/span><\/summary><div class=\"swell-block-accordion__body\" data-swl-acc=\"body\">\n<dl class=\"swell-block-dl u-mb-ctrl u-mb-60\">\n<dt class=\"swell-block-dl__dt\"><strong>Match Types<\/strong><\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<p>You can specify whether to perform an exact or approximate match:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><code>0<\/code><\/strong>: Exact match.<\/li>\n\n\n\n<li><strong><code>1<\/code><\/strong> or <strong><code>-1<\/code><\/strong>: Approximate match.<\/li>\n<\/ul>\n\n\n\n<p>This allows you to retrieve the closest value when an exact match isn\u2019t found, making it useful for working with ranges or approximate data.<\/p>\n<\/dd>\n\n\n\n<dt class=\"swell-block-dl__dt\"><strong>Search Modes<\/strong><\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<p>The search mode determines the order in which the search is conducted:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><code>1<\/code><\/strong>: Searches sequentially from the first to the last value.<\/li>\n\n\n\n<li><strong><code>-1<\/code><\/strong>: Searches in reverse, starting from the last value and moving upward.<\/li>\n<\/ul>\n\n\n\n<p>This feature gives you control over the order of results when there are multiple occurrences of the same value, helping you retrieve the desired data efficiently.<\/p>\n<\/dd>\n<\/dl>\n<\/div><\/details>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Basic Usage<\/h3>\n\n\n<div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-full is-resized\"><img decoding=\"async\" width=\"523\" height=\"429\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-63.png\" alt=\"\" class=\"wp-image-17211\" style=\"width:319px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-63.png 523w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-63-300x246.png 300w\" sizes=\"(max-width: 523px) 100vw, 523px\" \/><\/figure>\n<\/div>\n\n\n<p><strong><strong>Search in Ascending Order<\/strong><\/strong><\/p>\n\n\n\n<p>For example, if you want to search for the position of a specific product name in a product list from top to bottom:<\/p>\n\n\n<div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-full is-resized\"><img decoding=\"async\" width=\"440\" height=\"181\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-62.png\" alt=\"\" class=\"wp-image-17210\" style=\"width:261px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-62.png 440w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-62-300x123.png 300w\" sizes=\"(max-width: 440px) 100vw, 440px\" \/><\/figure>\n<\/div>\n\n\n<ul class=\"wp-block-list\">\n<li><strong>C2<\/strong>: The product name you want to search for.<\/li>\n\n\n\n<li class=\"u-mb-ctrl u-mb-30\"><strong>D2<\/strong>: The position of the product name.<\/li>\n<\/ul>\n\n\n\n<p>To return the position of the product name in <strong>D2<\/strong>, you can use the following XMATCH formula:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"=XMATCH(C2, A2:A, 0, 1)\" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F92672\">=<\/span><span style=\"color: #A6E22E\">XMATCH<\/span><span style=\"color: #F8F8F2\">(C2, A2:A, <\/span><span style=\"color: #AE81FF\">0<\/span><span style=\"color: #F8F8F2\">, <\/span><span style=\"color: #AE81FF\">1<\/span><span style=\"color: #F8F8F2\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro u-mb-ctrl u-mb-40\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])\n\" style=\"color:#90A4AE;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki material-theme-lighter\" style=\"background-color: #FAFAFA\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #39ADB5\">=<\/span><span style=\"color: #6182B8\">XMATCH<\/span><span style=\"color: #90A4AE\">(lookup_value<\/span><span style=\"color: #39ADB5\">,<\/span><span style=\"color: #90A4AE\"> lookup_array<\/span><span style=\"color: #39ADB5\">,<\/span><span style=\"color: #90A4AE\"> [match_mode]<\/span><span style=\"color: #39ADB5\">,<\/span><span style=\"color: #90A4AE\"> [search_mode])<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><\/div>\n\n\n\n<p>This formula searches for the product name <strong>&#8220;Apple&#8221;<\/strong> in ascending order (top to bottom) and returns the position of the first match it finds.<\/p>\n\n\n\n<p>In this case, the first occurrence of <strong>&#8220;Apple&#8221;<\/strong> is in <strong>cell A3<\/strong> (the second product name), so the XMATCH function returns <strong>2<\/strong>.<\/p>\n\n\n<div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-full is-resized\"><img decoding=\"async\" width=\"955\" height=\"432\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-61.png\" alt=\"\" class=\"wp-image-17209\" style=\"width:708px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-61.png 955w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-61-300x136.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-61-768x347.png 768w\" sizes=\"(max-width: 955px) 100vw, 955px\" \/><\/figure>\n<\/div>\n\n\n<p><strong>Descending Search<\/strong><\/p>\n\n\n\n<p class=\"has-f-8-f-8-f-2-color has-text-color\">Next, let&#8217;s demonstrate how to use the <strong>XMATCH<\/strong> function to search for a product name starting from the bottom of the list.<\/p>\n\n\n\n<p class=\"has-f-8-f-8-f-2-color has-text-color\">From the previous example, enter the following <strong>XMATCH<\/strong> formula in <strong>cell D2<\/strong>:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"=XMATCH(C2, A2:A, 0, -1)\" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F92672\">=<\/span><span style=\"color: #A6E22E\">XMATCH<\/span><span style=\"color: #F8F8F2\">(C2, A2:A, <\/span><span style=\"color: #AE81FF\">0<\/span><span style=\"color: #F8F8F2\">, <\/span><span style=\"color: #F92672\">-<\/span><span style=\"color: #AE81FF\">1<\/span><span style=\"color: #F8F8F2\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro u-mb-ctrl u-mb-40\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])\" style=\"color:#90A4AE;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki material-theme-lighter\" style=\"background-color: #FAFAFA\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #39ADB5\">=<\/span><span style=\"color: #6182B8\">XMATCH<\/span><span style=\"color: #90A4AE\">(lookup_value<\/span><span style=\"color: #39ADB5\">,<\/span><span style=\"color: #90A4AE\"> lookup_array<\/span><span style=\"color: #39ADB5\">,<\/span><span style=\"color: #90A4AE\"> [match_mode]<\/span><span style=\"color: #39ADB5\">,<\/span><span style=\"color: #90A4AE\"> [search_mode])<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>This formula searches for the product name <strong>&#8220;Apple&#8221;<\/strong> in descending order (bottom to top).<\/p>\n\n\n\n<p>As a result, the last occurrence of <strong>&#8220;Apple&#8221;<\/strong> is found in <strong>cell A7<\/strong> (the 6th product in the list), so the XMATCH function returns <strong>6<\/strong>.<\/p>\n\n\n<div class=\"wp-block-image u-mb-ctrl u-mb-60\">\n<figure class=\"alignleft size-full is-resized\"><img decoding=\"async\" width=\"959\" height=\"429\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-64.png\" alt=\"\" class=\"wp-image-17212\" style=\"width:720px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-64.png 959w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-64-300x134.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/image-64-768x344.png 768w\" sizes=\"(max-width: 959px) 100vw, 959px\" \/><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading u-mb-ctrl u-mb-30\">Conclusion<\/h2>\n\n\n\n<p>The <strong>XMATCH<\/strong> function is more flexible than the traditional <strong>MATCH<\/strong> function, enabling more advanced data analysis.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-30\">For simple searches, the MATCH function is often sufficient. However, when you need to perform searches regardless of the data&#8217;s order or use partial matches to find similar terms, the XMATCH function proves to be a powerful and convenient tool.<\/p>\n\n\n<div class=\"swell-block-postLink u-mb-ctrl u-mb-40\">\t\t\t<div class=\"p-blogCard -internal\" data-type=\"type1\" data-onclick=\"clickLink\">\n\t\t\t\t<div class=\"p-blogCard__inner\">\n\t\t\t\t\t<span class=\"p-blogCard__caption\">\u3042\u308f\u305b\u3066\u8aad\u307f\u305f\u3044<\/span>\n\t\t\t\t\t<div class=\"p-blogCard__thumb c-postThumb\"><figure class=\"c-postThumb__figure\"><img decoding=\"async\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/01\/a98b1120712ae2558c294773114e59b3-2-300x158.png\" alt=\"\" class=\"c-postThumb__img u-obf-cover\" width=\"320\" height=\"180\"><\/figure><\/div>\t\t\t\t\t<div class=\"p-blogCard__body\">\n\t\t\t\t\t\t<a class=\"p-blogCard__title\" href=\"https:\/\/84office.jp\/en\/googlesheets-xlookup\/\">Master XLOOKUP: Simplify Data Search and Learn the Key Differences from VLOOKUP and HLOOKUP<\/a>\n\t\t\t\t\t\t<span class=\"p-blogCard__excerpt\">Have you ever found yourself struggling to search for data in Google Sheets, especially when using traditional functions like VLOOKUP or HLOOKUP? As your dat&#8230;<\/span>\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/div>\n\n<div class=\"p-postListWrap u-mb-ctrl u-mb-40\"><ul class=\"p-postList -type-simple\"><li class=\"p-postList__item\">\n\t<a href=\"https:\/\/84office.jp\/en\/google-calendar-free-time-template\/\" class=\"p-postList__link\">\n\t\t<div class=\"p-postList__body\">\n\t\t\t<div class=\"p-postList__meta\">\n\t\t\t\t<div class=\"p-postList__times c-postTimes u-thin\">\n\t<time class=\"c-postTimes__posted icon-posted\" datetime=\"2025-07-25\" aria-label=\"\u516c\u958b\u65e5\">2025\u5e747\u670825\u65e5<\/time><\/div>\n\t\t<span class=\"p-postList__cat u-thin icon-folder\" data-cat-id=\"12\">Google Sheets<\/span>\n\t\t\t\t<\/div>\n\t\t\t<h2 class=\"p-postList__title\">Google Calendar Free Time Finder Template\uff5cGoogle Sheets \u00d7 Apps Script<\/h2>\t\t<\/div>\n\t<\/a>\n<\/li>\n<li class=\"p-postList__item\">\n\t<a href=\"https:\/\/84office.jp\/en\/google-calendar-free-time-extraction\/\" class=\"p-postList__link\">\n\t\t<div class=\"p-postList__body\">\n\t\t\t<div class=\"p-postList__meta\">\n\t\t\t\t<div class=\"p-postList__times c-postTimes u-thin\">\n\t<time class=\"c-postTimes__posted icon-posted\" datetime=\"2025-07-24\" aria-label=\"\u516c\u958b\u65e5\">2025\u5e747\u670824\u65e5<\/time><\/div>\n\t\t<span class=\"p-postList__cat u-thin icon-folder\" data-cat-id=\"12\">Google Sheets<\/span>\n\t\t\t\t<\/div>\n\t\t\t<h2 class=\"p-postList__title\">How to Find Common Free Time in Google Calendar: Manage Shared Schedules with Google Apps Script<\/h2>\t\t<\/div>\n\t<\/a>\n<\/li>\n<li class=\"p-postList__item\">\n\t<a href=\"https:\/\/84office.jp\/en\/google-calendar-to-sheet-template\/\" class=\"p-postList__link\">\n\t\t<div class=\"p-postList__body\">\n\t\t\t<div class=\"p-postList__meta\">\n\t\t\t\t<div class=\"p-postList__times c-postTimes u-thin\">\n\t<time class=\"c-postTimes__posted icon-posted\" datetime=\"2025-07-23\" aria-label=\"\u516c\u958b\u65e5\">2025\u5e747\u670823\u65e5<\/time><\/div>\n\t\t<span class=\"p-postList__cat u-thin icon-folder\" data-cat-id=\"12\">Google Sheets<\/span>\n\t\t\t\t<\/div>\n\t\t\t<h2 class=\"p-postList__title\">Ready-to-Use Google Calendar to Sheets Export Template| Google Sheets &amp; Apps Script<\/h2>\t\t<\/div>\n\t<\/a>\n<\/li>\n<\/ul><\/div>\n\n\n<p class=\"is-style-emboss_box has-swl-pale-02-background-color has-background\">Our company offers support for improving work efficiency through the use of Google Apps Script. <br>If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us. <br>We are fully committed to supporting your business improvements.<br><br><a href=\"https:\/\/84office.jp\/contact\/\">Contact us here<br><\/a><\/p>\n\n\n\n<script src=\"https:\/\/codoc.jp\/js\/cms.js\" data-css=\"rainbow\" data-usercode=\"pQLZofrBiA\" charset=\"UTF-8\" defer><\/script>\n <div id=\"codoc-entry-9lNr9PuMXQ\" class=\"codoc-entries\" data-without-body=\"1\" data-support-button-text=\"Support with a Tip\" data-support-message=\"We will keep offering tools and information to boost work efficiency. Your support helps us provide even better content.\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Google Sheets offers several convenient functions for searching data and retrieving its position. Among them,  [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17214,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"swell_btn_cv_data":"","_locale":"en_US","_original_post":"https:\/\/84office.jp\/?p=16424","footnotes":""},"categories":[12],"tags":[],"class_list":["post-17207","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-12","en-US"],"_links":{"self":[{"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/posts\/17207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/comments?post=17207"}],"version-history":[{"count":2,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/posts\/17207\/revisions"}],"predecessor-version":[{"id":17213,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/posts\/17207\/revisions\/17213"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/media\/17214"}],"wp:attachment":[{"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/media?parent=17207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/categories?post=17207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/tags?post=17207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}