{"id":419376,"date":"2022-03-21T18:00:55","date_gmt":"2022-03-21T15:00:55","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-find-values-with-index-in-microsoft-excel\/"},"modified":"2022-03-21T18:00:55","modified_gmt":"2022-03-21T15:00:55","slug":"how-to-find-values-with-index-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-find-values-with-index-in-microsoft-excel\/","title":{"rendered":"#How to Find Values With INDEX in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_89 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-6af257895add4\" 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-6af257895add4\" 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-values-with-index-in-microsoft-excel\/#%E2%80%9CHow_to_Find_Values_With_INDEX_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Find Values With INDEX 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-values-with-index-in-microsoft-excel\/#The_INDEX_Function_in_Excel\" >The INDEX 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-values-with-index-in-microsoft-excel\/#Use_INDEX_in_Array_Form\" >Use INDEX in Array Form<\/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-find-values-with-index-in-microsoft-excel\/#Use_INDEX_in_Reference_Form\" >Use INDEX in Reference Form<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Find_Values_With_INDEX_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Find Values With INDEX in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<!-- UNCACHED CONTENT --><br \/>\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 obtain a value that resides in a particular spot in your spreadsheet, you\u2019ll want one of Excel\u2019s lookup and reference functions. Here, we\u2019ll show you how to find values by location with INDEX in Excel.<\/p>\n<p>You might be creating a complex formula or have values that change often. By using the INDEX function, you designate the cell range along with a row number and column number. Excel locates the value at that intersection and provides the result from the formula.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"The_INDEX_Function_in_Excel\"><\/span>The INDEX Function in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You can use the INDEX function two different ways in Excel: Array Form and Reference Form.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791486\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SelectType-ExcelINDEXFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX function forms in Excel\" width=\"650\" height=\"297\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Array Form provides the value of a certain cell range, or array. Reference Form provides a reference to specific cells and is useful when working with nonadjacent cells.<\/p>\n<p>Let\u2019s look at how to use both.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_INDEX_in_Array_Form\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_1\">Use INDEX in Array Form<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The syntax for the function in Array Form is <code>INDEX(array, row_number, column_number)<\/code> where the first two arguments are required and <code>column_number<\/code> is optional.<\/p>\n<p>To find the value in the third row in the cell range C1 through C10, you would enter the following formula replacing the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/school\/microsoft-excel-formulas-and-functions\/lesson3\/\">cell references with your own.<\/p>\n<pre>=INDEX(C1:C10,3)<\/pre>\n<p>The number 3 here represents the third row. You don\u2019t need the column number argument because the array is in a single column, C.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791491\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ArrayRow-ExcelINDEXFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX in Array Form for a row\" width=\"470\" height=\"360\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To find the value in the third row and fifth column for the cell range A1 through E10, you would use this formula.<\/p>\n<pre>=INDEX(A1:E10,3,5)<\/pre>\n<p>Here, the <code>3<\/code> represents the third row and the <code>5<\/code> represents the fifth column. Because the array covers several columns, you should include the column number argument.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791492\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ArrayIntersection-ExcelINDEXFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX in Array Form for an intersection\" width=\"480\" height=\"360\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Number Rows in Microsoft Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_INDEX_in_Reference_Form\"><\/span>Use INDEX in Reference Form<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The syntax for the function in Reference Form is <code>INDEX(reference, row_number, column_number, area_number)<\/code> where the first two arguments are required and second two are optional.<\/p>\n<p>You can use a basic formula with the Reference Form of the function as with the Array Form. With the following formula, you receive the value in the third row and fifth column of cells A1 through E10, just like above.<\/p>\n<pre>=INDEX(A1:E10,3,5)<\/pre>\n<p>So, let\u2019s look at a more robust formula with this form of the INDEX function using nonadjacent cells.<\/p>\n<p>We\u2019re using two cell ranges here, A1 through E4 (first area) and A7 through E10 (second area). To find the value in the third row and fourth column in the first area, you would enter this formula:<\/p>\n<pre>=INDEX((A1:E4,A7:E10),3,4,1)<\/pre>\n<p>In this formula, you see the two areas, <code>3<\/code> for the third row, <code>4<\/code> for the fourth column, and <code>1<\/code> for the first area A1 through E4.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791489\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ReferenceFirstArea-ExcelINDEXFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX in Reference Form for area one\" width=\"650\" height=\"196\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To find the value using the same cell ranges, row number, and column number, but in the second area instead of the first, you would use this formula:<\/p>\n<pre>=INDEX((A1:E4,A7:E10),3,4,2)<\/pre>\n<p>As you can see, everything remains the same except you replace the <code>1<\/code> with a <code>2<\/code> for the second area.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791490\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ReferenceSecondArea-ExcelINDEXFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"INDEX in Reference Form for area two\" width=\"650\" height=\"196\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The INDEX function in Excel is a handy one to keep in mind. If you plan to use lookup and reference functions often, be sure to check out how to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/406053\/how-to-use-vlookup-on-a-range-of-values\/\">use VLOOKUP for a range of values and how to use 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\/791482\/index-function-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Find Values With INDEX in Microsoft Excel&#8221; When you need to obtain a value that resides in a particular spot in your spreadsheet, you\u2019ll want one of Excel\u2019s lookup and reference functions. Here, we\u2019ll show you how to find values by location with INDEX in Excel. You might be creating a complex formula&#8230;<\/p>\n","protected":false},"author":1,"featured_media":419377,"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-419376","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\/419376","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=419376"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/419376\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/419377"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=419376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=419376"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=419376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}