{"id":484430,"date":"2022-08-12T22:00:48","date_gmt":"2022-08-12T19:00:48","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-index-and-match-in-google-sheets\/"},"modified":"2022-08-12T22:00:48","modified_gmt":"2022-08-12T19:00:48","slug":"how-to-use-index-and-match-in-google-sheets","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-index-and-match-in-google-sheets\/","title":{"rendered":"#How to Use INDEX and MATCH in Google Sheets"},"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-6a2ed56da6be4\" 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-6a2ed56da6be4\" 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-use-index-and-match-in-google-sheets\/#%E2%80%9CHow_to_Use_INDEX_and_MATCH_in_Google_Sheets%E2%80%9D\" >&#8220;How to Use INDEX and MATCH in Google Sheets&#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-use-index-and-match-in-google-sheets\/#The_INDEX_Function\" >The INDEX Function<\/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-use-index-and-match-in-google-sheets\/#The_MATCH_Function\" >The MATCH Function<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-index-and-match-in-google-sheets\/#Using_INDEX_and_MATCH_Together\" >Using INDEX and MATCH Together<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Use_INDEX_and_MATCH_in_Google_Sheets%E2%80%9D\"><\/span>&#8220;How to Use INDEX and MATCH in Google Sheets&#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-731741\" data-pagespeed-no-defer=\"\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"1200\" height=\"675\"\/><\/p>\n<p>When you have a large amount of data in your spreadsheet, looking up specific data can take time. VLOOKUP is a handy function but has its limits. Instead, you can use INDEX and MATCH in Google Sheets.<\/p>\n<p>While VLOOKUP is a function that works well, it\u2019s only useful when the value you look up is to the left of what you want to return. For instance, if you want to return a value in column C, then the lookup value must be in column A or B.<\/p>\n<p>With INDEX and MATCH, you can have your lookup and return values anywhere in your sheet. This makes the duo a more useful option in many situations.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"The_INDEX_Function\"><\/span>The INDEX Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before you create the formula for INDEX and MATCH combined, it\u2019s good to know the syntax and arguments for each function separately.<\/p>\n<p>The syntax for the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/791482\/index-function-excel\/\">INDEX function is <code>INDEX(reference, row, column)<\/code> where only the first argument is required. Let\u2019s look at an example.<\/p>\n<p>Here, we want to return the value in row 2, column 1 in the cell range A1 through F13. We would use this formula:<\/p>\n<pre>=INDEX(A1:F13,2,1)<\/pre>\n<p>The formula gives us the result January.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-816613\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/INDEX-GoogleSheetsINDEXMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Formula for the INDEX function in Google Sheets\" width=\"650\" height=\"385\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"The_MATCH_Function\"><\/span>The MATCH Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Now let\u2019s try out the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/792150\/match-function-excel\/\">MATCH function which returns the position of a lookup value in a one-dimensional range.<\/p>\n<p>The syntax for the MATCH function is <code>MATCH(lookup, reference, search_type)<\/code> where the first two arguments are required. The <code>search_type<\/code> argument uses 1 as the default and assumes the reference is sorted in ascending order. You can use 0 to find an exact match or -1 if the reference is sorted in descending order.<\/p>\n<p>Here, we want to return the location of September in our range A1 through A13, so we\u2019d use this formula with an exact match <code>search_type<\/code>:<\/p>\n<pre>=MATCH(\"September\",A1:A13,0)<\/pre>\n<p>You can see we receive the result 10 because September is in row 10.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-816612\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/MATCH-GoogleSheetsINDEXMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Formula for the MATCH function in Google Sheets\" width=\"650\" height=\"385\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> If you look up text, be sure to include it in quotation marks.<\/p><\/blockquote>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Using_INDEX_and_MATCH_Together\"><\/span>Using INDEX and MATCH Together<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When you combine INDEX and MATCH in Google Sheets, you\u2019ll start with the INDEX function\u2019s formula. The MATCH portion of the formula fills the argument for position (row, column).<\/p>\n<p>Here, we want to look up the sales for pop sockets in January. We would use this formula:<\/p>\n<pre>=INDEX(F1:F13,MATCH(\"January\",A1:A13,0))<\/pre>\n<p>To break this down, MATCH looks up January in the range A1 through A13 as an exact match. It returns that position to INDEX which looks up the matching result in the range F1 through F13 and returns the value $888.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-816611\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/INDEXMATCHJanuary-GoogleSheetsINDEXMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Formula for the INDEX and MATCH functions in Google Sheets\" width=\"650\" height=\"371\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Let\u2019s look at one more example going the opposite direction of our sheet. Here, we want to look up the month that has pop socket sales of $777.00. We would use this formula:<\/p>\n<pre>=INDEX(A1:A13,MATCH(777,F1:F13,0))<\/pre>\n<p>To break this formula down, MATCH looks up 777 in the range F1 through F13 as an exact match. It returns that position to INDEX which looks up the matching result in the range A1 through A13 and returns the value October.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-816610\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/INDEXMATCH777-GoogleSheetsINDEXMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Formula for the INDEX and MATCH functions in Google Sheets\" width=\"650\" height=\"371\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The sheet in this tutorial contains a small amount of data for demonstration purposes. But when you have a sheet with hundreds or even thousands of rows or columns, INDEX and MATCH in Google Sheets can help you find the value you need, in any direction, quickly.<\/p>\n<p>For more, look at <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/800574\/index-match-vs-vlookup-vs-xlookup\/\">INDEX and MATCH versus VLOOKUP and XLOOKUP in Excel.<\/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\/816604\/index-match-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Use INDEX and MATCH in Google Sheets&#8221; When you have a large amount of data in your spreadsheet, looking up specific data can take time. VLOOKUP is a handy function but has its limits. Instead, you can use INDEX and MATCH in Google Sheets. While VLOOKUP is a function that works well, it\u2019s&#8230;<\/p>\n","protected":false},"author":1,"featured_media":484431,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-484430","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\/484430","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=484430"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/484430\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/484431"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=484430"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=484430"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=484430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}