[et_pb_section fb_built=”1″ module_class=”sheetgo-post” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”][et_pb_row _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n
The INDEX function<\/a> in Excel returns the value of a specified cell within an array. You essentially tell the function the array and the coordinates of the cell within the array, and the function returns the value of that cell. INDEX is useful on its own to pull data out of a larger dataset, or in combination with other functions like the MATCH function.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}”]=INDEX(array, row_num, [column_num])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n In its most basic form, the INDEX function takes coordinates in the form of a row and column number, and uses those coordinates to find a cell within the specified array.<\/p>\n This is demonstrated in the example below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/1.-INDEX-basic-example.png” alt=”INDEX Excel 1 basic example” title_text=”1. INDEX basic example” align=”center” _builder_version=”4.23.1″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n Here I\u2019ve used the formula =INDEX(A2:B6,3,2)<\/b> to return the depth of the Atlantic Ocean. The function looks within the array A2:B6, and finds the cell in the third row and second column. As you can see, it properly returns the value in that cell!<\/p>\n It\u2019s important to note that the INDEX function is a reference function, which means it doesn\u2019t perform any calculation of its own.<\/p>\n Basically, all it does is locate a cell and reference the value within that cell. To demonstrate this, watch what happens when I change the value in the referenced cell.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/2.-INDEX-reference-cell.png” alt=”INDEX Excel 2 reference cell” title_text=”2. INDEX reference cell” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n I changed the Atlantic depth to 100, and the reference cell reflected the change. I didn\u2019t touch the formula in E3, but it updated all on its own!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n The INDEX function is most commonly used in Array form, which is what I just showed. However, there\u2019s an alternative form of this function called the Reference form.<\/p>\n In Reference form, the INDEX function can take multiple input arrays.<\/p>\n In order to do this, you have to specify which array you want to search. You can do this by adding a fourth parameter to the function:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}”]=INDEX(reference, row_num, [column_num], [area_num])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n The area_num<\/b> parameter tells the function which reference array to look in. If left blank, it defaults to the first array.<\/p>\n To get a better idea of how INDEX works in Reference form, take a look at the next example.<\/p>\n I now have three tables of data. I can use the Reference form of the INDEX function to incorporate all three within a single function by typing =INDEX((A2:B6,D2:E6,G2:H6),5,2,2)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/3.-INDEX-reference-form.png” alt=”INDEX Excel 3 reference form” title_text=”3. INDEX reference form” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n As you can see from the formula, you can enter all three arrays for the reference parameter. Make sure you enclose them in brackets and separate them with commas.<\/p>\n Next, specify the row and column numbers to reference, just like in the earlier example.<\/p>\n Finally, there\u2019s the area_num<\/b> parameter at the end. This parameter (2) tells the function to look in the second array. After typing all this in, I get the correct result pulled from the data!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n As shown in the examples above, the INDEX function is very useful on its own. But like many functions, it really hits its stride when combined with other functions.<\/p>\n One useful example is combining INDEX with COUNTA.<\/p>\n This combination lets you automatically reference the last entry in a dataset, so that your INDEX function will always reference the most recent data. This is handy when you have a live spreadsheet that is updated regularly.<\/p>\n In the example below, I have some data on weekly rainfall. This table is updated every week, and I want to quickly have a reference to the latest numbers. I achieve this by typing =INDEX(A:B,(COUNTA(A:A)),2)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/4.-INDEX-with-COUNTA.png” alt=”INDEX Excel 4 index with counta” title_text=”4. INDEX with COUNTA” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n Now how does this work? The function uses the entire A and B columns as its reference array, which means anything added to those columns will fall within the array.<\/p>\n To specify which row I want to reference, I use the COUNTA function to count how many rows are populated. If 5 rows are populated, then I want to reference row 5 \u2013 COUNTA makes this happen.<\/p>\n Now comes the real test: does the function automatically update when I add new data? In the screenshot below I\u2019ve added week 5, and you can see that the function automatically changed to show this new data!<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/5.-INDEX-with-COUNTA-updated.png” alt=”INDEX Excel 5 index with counta” title_text=”5. INDEX with COUNTA updated” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}”]<\/p>\n Clearly, the INDEX function in Excel is quite powerful when combined with other functions.<\/p>\n Another common combination is INDEX and MATCH<\/a>, which combines to form a more flexible version of the VLOOKUP function<\/a>. I encourage you to check it out!<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The INDEX function in Excel returns the value of a specified cell within an array. You essentially tell the function the array and the coordinates of the cell within the array, and the function returns the value of that cell. INDEX is useful on its own to pull data out of a larger dataset, or […]<\/p>\n","protected":false},"author":40,"featured_media":32241,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[39,28],"class_list":["post-32240","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/32240","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=32240"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/32240\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/32241"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=32240"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=32240"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=32240"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Syntax<\/h3>\n
\n
How to use the INDEX Excel function<\/h3>\n
Reference form<\/h4>\n
INDEX with COUNTA<\/h4>\n
Final thoughts<\/h3>\n