Syntax<\/h3>\n
INDEX(reference, [row_offset], [column_offset])<\/strong><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s take a look at some practical examples to understand how to use this function.<\/p>\n In the following examples I’ve used some fictitious data to show the INDEX function in use.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-1.png” alt=”INDEX-function-example-one” title_text=”INDEX example 1″ _builder_version=”4.23.1″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the above screenshot, all the cases except the last one are pretty straightforward.<\/p>\n Essentially, you give the formula a range of cells, then give it the coordinates of the cell in the range that you want it to return as the result. So in row 3<\/strong>, where the formula is =INDEX(A2:B11,8,2), I\u2019m telling the formula that I want the cell in row 8, column 2 of the array, which is $13,947.<\/p>\n In the last example in the screenshot (row 5<\/strong>) I did not specify any row or column offsets. As a result, the function returned the complete set of values from the input reference range. If anything, this could serve as an input for another array formula. For instance, you could use this to consolidate data from multiple sheets, or you could do something like =COUNTA(INDEX(A2:A8)), that would have returned the value 7.<\/p>\n Are there any cases where INDEX function might throw up an error? Of course, yes! When you try to point the function away from the confines of the input range, it does cough up an error, as shown in the screenshot below:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-2.png” alt=”Index-function-google-sheetsexample-2″ title_text=”INDEX example 2″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here I’ll take a look at some use cases that demonstrate how Google Sheets INDEX function can be used in combination with other functions and formulas.<\/p>\n Combining the COUNTA function with the INDEX function can be useful in situations where, for instance, you always want to perform a calculation using the last row of data in a list<\/strong> that is regularly being added to.<\/p>\n In the following screenshot, I have a spreadsheet that gets updated every week with the average temperature for that week.<\/p>\n If I always want to perform a calculation with the most recent week\u2019s average temperature, I can use COUNTA and INDEX to always select the last entry, using the formula =INDEX((A:B), COUNTA(A:A),2)<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-3.png” alt=”INDEX-function-google-sheets-example-3″ title_text=”INDEX example 3″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here, the entire A and B columns are the range. The COUNTA(A:A) function counts the number of data points in column A and tells you how many there are, and the INDEX formula takes that number as the row that it will get its result from.<\/p>\n The 2 at the end tells the formula that it will get the result from the second column of the range (column B). So no matter how many weeks you add to the list, the \u201cMost recent week\u201d will always stay up-to-date!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Perhaps one of the most powerful uses of INDEX function in Google Sheets is when it is used along with MATCH, in order to look up values.<\/p>\n But there is already a VLOOKUP formula in Google Sheets for that purpose, right?<\/p>\n Take a look at the example below:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-4.png” alt=”INDEX-function-google-sheets-example-4″ title_text=”INDEX example 4″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The VLOOKUP formula in D2 looks up 161 in the Emp ID #<\/strong> column (as it\u2019s the leftmost in the range A2:B11), and from the row where it finds the value 161, it fetches the value located in the second column (i.e. Column B), while assuming the data is not sorted.<\/p>\n So far, so good. But there are two critical problems with VLOOKUP in Google Sheets.<\/p>\n <\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections B – Merge, split, filter – Vertical” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” text_font=”||||||||” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”50477″ theme_builder_area=”post_content”]<\/p>\n Merge, split, and filter spreadsheets<\/span> [\/et_pb_text][et_pb_text _builder_version=”4.27.2″ hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Before going further with examples of how to use the INDEX and MATCH functions in combination, I will outline two critical issues with the VLOOKUP, in order to give you an idea of the limitations of this function and when to use INDEX and MATCH instead.<\/p>\n What happens when you insert a new column between the first and second columns? Let\u2019s try that:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-5.png” alt=”index-formula-google-sheets-example-5″ title_text=”INDEX example 5″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You\u2019ll notice the returned value is not “Ethan” anymore. This is because VLOOKUP is a semi-static formula.<\/p>\n Google Sheets updated the second parameter to reflect the new range, but it did not accordingly change the column index (third parameter) when a new column was added before the Salesperson<\/strong> column.<\/p>\n Situations may arise where you might have to lookup values from a column (Emp ID #) that is not the leftmost, as shown below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-6.png” alt=”index-function-google-sheets-example-6″ title_text=”INDEX example 6″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this case, moving the Emp ID #<\/strong> column to make it the leftmost would work. But that isn\u2019t an ideal approach \u2013 there can be data layout or presentation specifications that do not allow you to re-arrange columns.<\/p>\n In that situation, what can you do? The MATCH and INDEX combination comes to the rescue. Here\u2019s the syntax, followed by a few examples.<\/p>\n INDEX(reference, MATCH(search_key, range, search_type))<\/strong><\/p>\n The key to this combination is that both the ranges selected for the INDEX and MATCH functions, respectively, need to be a single column.<\/p>\n You are essentially using a VLOOKUP, but specifying the column to look in, and the column to return the value from, in separate ranges within the MATCH and INDEX functions As you\u2019ll see, this methodology helps you avoid the errors that can occur using VLOOKUP.<\/p>\n I\u2019ll give a brief summary of the MATCH function here for those who are not familiar with it. The MATCH function takes a given search_key, which is that value that you\u2019re telling it to look for within a certain range. Unlike with the INDEX function, this needs to be one-dimensional (within just one column or one row).<\/p>\n The MATCH function gives you the location of the item you\u2019ve told it to search for. For search_type you typically say 0, which tells the function it needs to be an exact match.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-7.png” alt=”index-function-example-7″ title_text=”INDEX example 7″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the above example, I\u2019ve used the MATCH function to tell me which row contains Emp ID #161 within column C. The function returns a number that tells the INDEX function which row in column A to look for the Salesperson. The result is that the two functions combine to tell me the correct name (Ethan) for Emp ID #161. The formula I used to do this is =INDEX(A2:A11,MATCH(161,C2:C11,0)).<\/p>\n As you can see, unlike VLOOKUP, the combination works even if the lookup column is not the leftmost (first three examples in the above screenshot). Not surprisingly, it also works like VLOOKUP, when the lookup column is the leftmost.<\/p>\n Let\u2019s also see whether the functions hold up when you introduce a new column in between.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-8.png” alt=”index-function-google-sheets-example-8″ title_text=”INDEX example 8″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Thankfully, they still work. As soon as I introduced a new column (State), Google Sheets updated the references automatically to accommodate this change.<\/p>\n As you\u2019ve seen above, the MATCH and INDEX function combination is much more flexible and versatile than the already popular and powerful VLOOKUP function. But the combination of these functions can be even more powerful when you use two MATCH functions instead of just one.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You\u2019ve now seen how useful the INDEX and MATCH combination can be. But what if you have a bi-dimensional array from which you want to get a value? In this case, you can use the MATCH function twice within the INDEX function, as shown in the example below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/10\/INDEX-example-9-1.png” alt=”index-function-google-sheets-example-9″ title_text=”INDEX example 9 (1)” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this example, you can see data on the average temperature in each month, for the years 2015-2020. If you want to pull the average temperature of a specific month of a specific year out of this array, you can use the INDEX and MATCH combination to accomplish this. The function used here is:<\/p>\n =INDEX(B2:M7, Match(B11, A2:A7, false), Match(B10, B1:M1, false))<\/p>\n As you can see, I am using the MATCH function to give me the location of the row for the year I am looking for (in this case it\u2019s 2018, which is in the 4th row of data) as well as the column for the month I am looking for (September, in the 9th column of data). The INDEX function then takes these coordinates and returns the average temperature for September 2018.<\/p>\n By using the INDEX and MATCH functions, you can not only get a search function that is more versatile than VLOOKUP, but you can also search bi-dimensional arrays. Learn more about Google Sheets MATCH function here<\/a>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you found this article useful, share it with your network via the social media buttons on the left!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ global_module=”26540″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The INDEX function in Google Sheets returns the value of a cell within an input range, relatively separated from the first cell by row and column offsets. This is similar to the index at the end of a book, which provides a quick way to locate specific content. Syntax INDEX(reference, [row_offset], [column_offset]) reference \u2013 the […]<\/p>\n","protected":false},"author":40,"featured_media":27673,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The INDEX<\/a> formula in Google sheets returns the value of a cell within an input range, relatively separated from the first cell by row and column offsets. This is similar to the index at the end of a book, that gives us a quick way to pick a content.\n\n
How to use the INDEX function in Google Sheets<\/h3>\n
How to use INDEX function in combination with other formulas<\/h3>\n
Use Case #1: INDEX function and COUNTA function<\/h4>\n
Use Case #2: combining MATCH and INDEX functions<\/h3>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
Regardless of the file format, Sheetgo can handle it.<\/span><\/p>\n![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/available-on-google-workspace-button-text.webp\")
<\/a><\/p>\nProblems with VLOOKUP that can be solved using the INDEX and MATCH functions<\/h3>\n
Problem #1: Static cell referencing<\/h4>\n
Problem #2: Lookup column is always the leftmost<\/h4>\n
MATCH function<\/h3>\n
Use case #3: INDEX function with two MATCH functions<\/h3>\n
Did you like this post?<\/h4>\n
Syntax<\/h3>\nINDEX(reference, [row_offset], [column_offset])<\/span><\/strong>\n
\n \t
Usage: INDEX Formula<\/h3>\nLet us practically apply the formula to simplify our understanding process. Consider the following fictitious data and we tried a few example cases.\n\n
![\"INDEX](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/index1.png\")
\n\nAll the cases except the last one are pretty straight forward. The last one though, where we did not specify any row or column offsets, returned the complete set of values from the input reference range. If anything, that could serve as an input to another array formula. For instance, we could do something like =COUNTA(INDEX(A2:A8)), that would have returned the value 7.\n\nCan we think of any cases where the formula might throw up an error? Of course, yes! When we try to point the formula away from the confines of the input range, it does cough up an error, as shown in the snapshot below.\n\n![\"INDEX](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/index2.png\")
\nUse Case: MATCH and INDEX formula combination<\/h5>\nPerhaps one of the most powerful uses of INDEX formula in Google Sheets is when we use it along with the MATCH<\/a> formula, in order to lookup values. But we already have VLOOKUP<\/a> formula in Google Sheets for this purpose, don\u2019t we? We have an example below:\n\n
![\"INDEX](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/index3.png\")
\n\nThe VLOOKUP formula in D2 looks up 161 in the 'Emp ID #' column (as it the left most in the range A2:B11), and from the row where there is the value 161, it fetches the value located in the second column (i.e. column B), while assuming the data is not sorted. So far, so good. But we have two critical problems with using VLOOKUP in Google Sheets.\nProblem # 1<\/strong>: Static cell referencing.<\/h5>\nWhat happens when we insert a new column between the first and second columns? Let\u2019s try that.\n\n
![\"INDEX](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/index4.png\")
\n\nYou\u2019ll notice the returned value is not \u2018Ethan<\/strong>\u2019 anymore. Because, it was a semi-static formula. Google Sheets updated the second parameter to reflect the new range, but it did not accordingly change the column index (third parameter) when we added a new column before the \u2018Salesperson<\/strong>\u2018 column.\nProblem # 2<\/strong>: Lookup column is always the left most<\/h5>\nSituations may arise where the we might have to lookup values from a column (Emp ID #) that is not the left most, as shown below.\n\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/index5.png\")
\n\nProbably, move the Emp ID # column to make it the left most? Good idea. But, that isn\u2019t an ideal approach. Because, there can be data layout or presentation specifications that do not allow us to re-arrange columns. What, in such a situation, is the solution? The MATCH and INDEX formula combination comes to our rescue. Here\u2019s the combination syntax, followed by a few examples.\n\nINDEX(reference, MATCH(search_key, range, search_type))<\/strong>\n\n![\"INDEX](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/index6.png\")
\n\nAs we can see, unlike VLOOKUP, the combination works even if the lookup column is not the left most (first three examples). Not surprisingly, it also works like VLOOKUP, when the lookup column is the left most.\n\nAlso, let\u2019s see whether the formulas hold good, when we introduce a new column in between.\n\n![\"INDEX](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/index7.png\")
\n\nThankfully, yes, they hold good. As soon as we introduced a new column (State), Google Sheets updated the references automatically to accommodate this change. Therefore, the MATCH and INDEX formula combination is much more flexible and versatile than the already popular and powerful VLOOKUP formula.\n\nLearn more about the MATCH formula to the following blog post: \"How to use the MATCH formula in Google Sheets<\/a><\/strong>\"","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3567","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/3567","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=3567"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/3567\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/27673"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=3567"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=3567"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=3567"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}