{"id":430304,"date":"2022-04-11T18:00:00","date_gmt":"2022-04-11T15:00:00","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-index-and-match-in-microsoft-excel\/"},"modified":"2022-04-11T18:00:00","modified_gmt":"2022-04-11T15:00:00","slug":"how-to-use-index-and-match-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-index-and-match-in-microsoft-excel\/","title":{"rendered":"#How to Use INDEX and MATCH in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 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-6a36c3f18fa96\" 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-6a36c3f18fa96\" 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-microsoft-excel\/#%E2%80%9CHow_to_Use_INDEX_and_MATCH_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Use INDEX and 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-use-index-and-match-in-microsoft-excel\/#VLOOKUP_Versus_INDEX_and_MATCH\" >VLOOKUP Versus INDEX and MATCH<\/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-microsoft-excel\/#INDEX_and_MATCH_Function_Basics\" >INDEX and MATCH Function Basics<\/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-microsoft-excel\/#How_to_Use_INDEX_and_MATCH_in_Excel\" >How to Use INDEX and MATCH in Excel<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Use_INDEX_and_MATCH_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Use INDEX and 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>While the VLOOKUP function is good for finding values in Excel, it has its limitations. With a combination of the INDEX and MATCH functions instead, you can look up values in any location or direction in your spreadsheet.<\/p>\n<p>The INDEX function returns a value based on a location you enter in the formula while MATCH does the reverse and returns a location based on the value you enter. When you combine these functions, you can find any number or text you need.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"VLOOKUP_Versus_INDEX_and_MATCH\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_0\">VLOOKUP Versus INDEX and MATCH<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The difference between these functions and VLOOKUP is that VLOOKUP finds values from left to right. Hence the function\u2019s name; VLOOKUP performs a vertical lookup.<\/p>\n<p>Microsoft best explains <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/support.microsoft.com\/en-us\/office\/look-up-values-with-vlookup-index-or-match-68297403-7c3c-4150-9e3c-4d348188976b\">the way VLOOKUP works<\/a>:<\/p>\n<blockquote><p>There are certain limitations with using VLOOKUP\u2014the VLOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value.<\/p>\n<\/blockquote>\n<p>Microsoft goes on to say that if your sheet isn\u2019t set up in a way where VLOOKUP can help you find what you need, you can use INDEX and MATCH instead. So let\u2019s look at how to use INDEX and MATCH in Excel.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"INDEX_and_MATCH_Function_Basics\"><\/span>INDEX and MATCH Function Basics<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To use these functions together, it\u2019s important to understand their purpose and structure.<\/p>\n<p>The syntax for INDEX in Array Form is <code>INDEX(array, row_number, column_number)<\/code> with the first two arguments required and the third optional.<\/p>\n<p>INDEX looks up a position and returns its value. To find the value in the fourth row in the cell range D2 through D8, you would enter the following formula:<\/p>\n<pre>=INDEX(D2:D8,4)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793311\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/INDEX-ExcelINDEXandMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX function in Excel\" width=\"650\" height=\"242\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The result is 20,745 because that\u2019s the value in the fourth position of our cell range.<\/p>\n<p>For more details on the Array and Reference Forms of INDEX as well as other ways to use this function, take a look at our how-to for <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/791482\/index-function-excel\/\">INDEX in Excel.<\/p>\n<p>The syntax for MATCH is <code>MATCH(value, array, match_type)<\/code> with the first two arguments required and the third optional.<\/p>\n<p>MATCH looks up a value and returns its position. To find the value in cell G2 in the range A2 through A8, you would enter the following formula:<\/p>\n<pre>=MATCH(G2,A2:A8)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793310\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MATCH-ExcelINDEXandMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"MATCH function in Excel\" width=\"650\" height=\"242\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The result is 4 because the value in cell G2 is in the fourth position in our cell range.<\/p>\n<p>For additional details on the <code>match_type<\/code> argument and other ways to use this function, take a look at our tutorial for MATCH in Excel.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Find a Value&#8217;s Position With MATCH in Microsoft Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Use_INDEX_and_MATCH_in_Excel\"><\/span>How to Use INDEX and MATCH in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Now that you know what each function does and its syntax, it\u2019s time to put this dynamic duo to work. Below, we\u2019ll use the same data as above for INDEX and MATCH individually.<\/p>\n<p>You\u2019ll place the formula for the MATCH function inside the formula of the INDEX function in place of the position to look up.<\/p>\n<p>To find the value (sales) based on the location ID, you would use this formula:<\/p>\n<pre>=INDEX(D2:D8,MATCH(G2,A2:A8))<\/pre>\n<p>The result is 20,745. MATCH finds the value in cell G2 within the range A2 through A8 and provides that to INDEX which looks to cells D2 through D8 for the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793313\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/INDEXMATCHG2-ExcelINDEXandMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX and MATCH for a cell reference\" width=\"650\" height=\"242\" 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 another example. We want to know which city has sales that match a certain amount. Using our sheet, you would enter this formula:<\/p>\n<pre>=INDEX(B2:B8,MATCH(G5,D2:D8))<\/pre>\n<p>The result is Houston. MATCH finds the value in cell G5 within the range D2 through D8 and provides that to INDEX which looks to cells B2 through B8 for the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793312\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/INDEXMATCHG5-ExcelINDEXandMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX and MATCH for a cell reference\" width=\"650\" height=\"242\" 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 an actual value instead of a cell reference. We\u2019ll look for the value (sales) for a specific city with this formula:<\/p>\n<pre>=INDEX(D2:D8,MATCH(\"Houston\",B2:B8))<\/pre>\n<p>In the MATCH formula, we replaced the cell reference containing the lookup value with the actual lookup value of \u201cHouston\u201d from B2 through B8 which gives us the result 20,745 from D2 through D8.<\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> Be sure when you use the actual value to look up, rather than a cell reference, that you enclose it in quotes as shown here.<\/p><\/blockquote>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793316\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/INDEXMATCHHouston-ExcelINDEXandMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX and MATCH for text\" width=\"650\" height=\"242\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To obtain that same result by using the location ID instead of the city, we simply change the formula to this:<\/p>\n<pre>=INDEX(D2:D8,MATCH(\"2B\",A2:A8))<\/pre>\n<p>Here we changed the MATCH formula to look up \u201c2B\u201d in the cell range A2 through A8 and provide that result to INDEX which then returns 20,745.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793315\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/INDEXMATCH2B-ExcelINDEXandMATCH.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX and MATCH for text\" width=\"650\" height=\"242\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/765624\/basic-excel-functions-everybody-should-know\/\">Basic functions in Excel like those that help you add numbers in cells or enter the current date are certainly helpful. But when you start adding more data and advancing your data entry or analysis needs, lookup functions like INDEX and MATCH in Excel can be quite useful.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>12 Basic Excel Functions Everybody Should Know<\/em><\/strong><\/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>\n<\/p><\/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\/793270\/index-and-match-in-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Use INDEX and MATCH in Microsoft Excel&#8221; While the VLOOKUP function is good for finding values in Excel, it has its limitations. With a combination of the INDEX and MATCH functions instead, you can look up values in any location or direction in your spreadsheet. The INDEX function returns a value based on&#8230;<\/p>\n","protected":false},"author":1,"featured_media":430305,"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-430304","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\/430304","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=430304"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/430304\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/430305"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=430304"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=430304"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=430304"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}