{"id":422771,"date":"2022-03-28T18:00:00","date_gmt":"2022-03-28T15:00:00","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-find-a-values-position-with-match-in-microsoft-excel\/"},"modified":"2022-03-28T18:00:00","modified_gmt":"2022-03-28T15:00:00","slug":"how-to-find-a-values-position-with-match-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-find-a-values-position-with-match-in-microsoft-excel\/","title":{"rendered":"#How to Find a Value\u2019s Position With MATCH in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a28dc4a9cee5\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a28dc4a9cee5\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-find-a-values-position-with-match-in-microsoft-excel\/#%E2%80%9CHow_to_Find_a_Values_Position_With_MATCH_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Find a Value\u2019s Position With MATCH in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-find-a-values-position-with-match-in-microsoft-excel\/#What_Is_the_MATCH_Function_in_Excel\" >What Is the MATCH Function in Excel?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-find-a-values-position-with-match-in-microsoft-excel\/#Use_MATCH_in_Excel\" >Use MATCH in Excel<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Find_a_Values_Position_With_MATCH_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Find a Value\u2019s Position With MATCH in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When you need to find a value\u2019s exact position in your spreadsheet, you can use the MATCH function in Excel. This saves you from manually searching for the location that you may need for reference or another formula.<\/p>\n<p>The MATCH function is often used with the INDEX function as an advanced lookup. But here, we\u2019ll walk through how to use MATCH on its own to find a value\u2019s spot.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"What_Is_the_MATCH_Function_in_Excel\"><\/span>What Is the MATCH Function in Excel?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The MATCH function in Excel searches for a value in the array, or range of cells, that you specify.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Find the Function You Need in Microsoft Excel<\/em><\/strong><\/p>\n<p>For instance, you might look up the value 10 in the cell range B2 through B5. Using MATCH in a formula, the result would be 3 because the value 10 is in the third position of that array.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792155\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/Position-ExcelMATCHFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Position of a value in a cell range\" width=\"255\" height=\"175\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The syntax for the function is <code>MATCH(value, array, match_type)<\/code> where the first two arguments are required and <code>match_type<\/code> is optional.<\/p>\n<p>The match type can be one of the following three options. If the argument is omitted from the formula, 1 is used by default.<\/p>\n<ul>\n<li><strong>1<\/strong>: Finds the largest value less than or equal to the searched <code>value<\/code>. The range must be in ascending order.<\/li>\n<li><strong>0<\/strong>: Finds the value exactly equal to the searched <code>value<\/code> and the range can be in any order.<\/li>\n<li><strong>-1<\/strong>: Finds the smallest value greater than or equal to the searched <code>value<\/code>. The range must be in descending order.<\/li>\n<\/ul>\n<p>You may also see these match types as a tooltip when you build your formula in Excel.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792154\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MatchTypes-ExcelMATCHFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Match type tool tip in Excel\" width=\"650\" height=\"121\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The MATCH function is not case sensitive, allows the wildcards asterisk (*) and question mark (?), and returns #N\/A as the error if no match is found.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_MATCH_in_Excel\"><\/span>Use MATCH in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When you\u2019re ready to put the MATCH function to work, take a look at these examples to help you walk through building the formula.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>Defining and Creating a Formula<\/em><\/strong><\/p>\n<p>Using our example above, you would use this formula to find the value 10 in the range B2 through B5. Again, our result is 3 representing the third position in the cell range.<\/p>\n<pre>=MATCH(10,B2:B5)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792156\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/Match10-ExcelMATCHFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"MATCH used to look up a value's position\" width=\"475\" height=\"240\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For another example, we\u2019ll include the match type 1 at the end of our formula. Remember, match type 1 requires the array be in ascending order.<\/p>\n<pre>=MATCH(10,B2:B5,1)<\/pre>\n<p>The result is 4 which is the position of the number 9 in our range. That\u2019s the highest value less than or equal to 10.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-792160 size-full\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MatchType1-ExcelMATCHFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"MATCH with match type 1\" width=\"495\" height=\"240\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Here\u2019s an example using match type 0 for an exact match. As you can see, we receive the #N\/A error because there is no exact match to our value.<\/p>\n<pre>=MATCH(10,B2:B5,0)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792157\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MatchType0-ExcelMATCHFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"MATCH with match type 0\" width=\"495\" height=\"240\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Let\u2019s use the final match type -1 in this formula.<\/p>\n<pre>=MATCH(10,B2:B5,-1)<\/pre>\n<p>The result is 2 which is the position of the number 11 in our range. That\u2019s the lowest value greater than or equal to 10. Again, match type -1 requires the array be in descending order.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792159\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MatchType-1-ExcelMATCHFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"MATCH with match type -1\" width=\"495\" height=\"240\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For an example using text, here we can find Caps in the cell range A2 through A5. The result is 1 which is the first position in our array.<\/p>\n<pre>=MATCH(\"Caps\",A2:A5)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792158\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MatchText-ExcelMATCHFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"MATCH for finding text in Excel\" width=\"510\" height=\"240\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Now that you know the basics of using the MATCH function in Excel, you might also be interested in learning about using XLOOKUP in Excel or using VLOOKUP for a range of values.<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/792150\/match-function-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Find a Value\u2019s Position With MATCH in Microsoft Excel&#8221; When you need to find a value\u2019s exact position in your spreadsheet, you can use the MATCH function in Excel. This saves you from manually searching for the location that you may need for reference or another formula. The MATCH function is often used&#8230;<\/p>\n","protected":false},"author":1,"featured_media":422772,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-422771","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/422771","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=422771"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/422771\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/422772"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=422771"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=422771"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=422771"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}