You use the second MATCH function when searching a bi-dimensional array of data, for example if you need the MATCH function to identify both the vertical and horizontal offset.<\/li>\n<\/ul>\n[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Using the INDEX MATCH combination<\/h4>\n
The syntax above looks complicated, but I\u2019ll give an example using the INDEX and MATCH combination in order to demonstrate how simple it really is.<\/p>\n
Take a look at the screenshot below. I want the spreadsheet to tell me which person has a test score of 83. The formula I use for this example is\u00a0=INDEX(A2:A9,MATCH(83,C2:C9,0))<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/01\/3.-Excel-index-match-function.png” alt=”index match excel 3″ title_text=”3. Excel index match function” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
I’ve used the INDEX function, but instead of hardcoding the row number that I want it to return (like in the first example when I asked the INDEX function to return the third name of the list), I use the MATCH function to dynamically input the row that I want. The MATCH function takes my input (test score of 83) and tells the INDEX function in which row the number 83 appears. The INDEX function then takes that row number and selects the corresponding name (Emily).<\/p>\n
At this point you might think “why don\u2019t you just use the VLOOKUP function to do this?. Why bother combining two separate functions?” Well, there are a few different reasons, and they have to do with the limitations of the VLOOKUP function.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Limitations of the VLOOKUP function<\/h4>\n
The VLOOKUP function searches for a given input within an array, then outputs the data from another column corresponding to the row in which the input value was found. It is a very useful function, but it does have some limitations which I\u2019ll outline below.<\/p>\n
But first, here\u2019s an example in which I use the VLOOKUP function to find the test score of Emily within the dataset.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/01\/4.-Excel-index-match-Vlookup-function.png” alt=”index match excel 4″ title_text=”4. Excel index match Vlookup function” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Limitation 1 of VLOOKUP<\/h5>\n
The first limitation of the VLOOKUP function is that it can only be used to return the value in the leftmost column of the data. This wasn\u2019t an issue in the previous example because there, I searched for the name of the student.<\/p>\n
However, if I want to find the test score of Emily (by inputting \u201cEmily\u201d as the phrase to search for),\u00a0 the function returns an #N\/A<\/b> error. I asked to return a value (Test score) that is not in the leftmost column of the array.[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/01\/5.-Excel-index-match-Vlookup-leftmost-column.png” alt=”index match excel 5″ title_text=”5. Excel index match Vlookup leftmost column” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\nLimitation 2 of VLOOKUP<\/h5>\n
The second limitation of the VLOOKUP function is that it is a static function that does not automatically update when you add new columns.<\/p>\n
In the example below, I’ve used the same VLOOKUP function as in the previous example (finding the test score of Emily) and I’ve added a column to indicated whether or not each student lives on campus.<\/p>\n
If the VLOOKUP function automatically updated, there would be no change to the result and I would still get Emily as the output. This is not the case however: the function returns 22 as the result.<\/p>\n
This is obviously not the value I was searching for, and is a good example of how the VLOOKUP function won\u2019t adapt to changes you make in your spreadsheet (like adding a new column for example).<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/01\/6.-Excel-index-match-Vlookup-column-added.png” alt=”index match excel 6″ title_text=”6. Excel index match Vlookup column added” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
If I do the same thing with an INDEX MATCH function on the other hand, the result doesn’t change.<\/p>\n
Take a look at he example below. I\u2019ve added an extra column to the data, and the INDEX MATCH function still works as a charm.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/01\/7.-Excel-index-match-vs.-Vlookup.png” alt=”index match excel 7″ title_text=”7. Excel index match vs. Vlookup” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
INDEX MATCH with two matches (Bi-dimensional array)<\/h4>\n
By now you\u2019ve seen how useful the INDEX MATCH combination can be. But what if you have data that is 2-dimensional? In that case, you should use the INDEX function with two MATCH functions to get the value you\u2019re looking for.<\/p>\n
Here, you see a variety of test scores for different subjects of each student. The INDEX function combined with two MATCH functions identifies the test score for a specific student and a specific subject. <\/p>\n
The function I use here is =INDEX(B2:F9,MATCH(B12,A2:A9,FALSE),MATCH(B13,B1:F1,FALSE))<\/b>.[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/01\/8.-Excel-index-match-bidirectional.png” alt=”index match excel 8″ title_text=”8. Excel index match bidirectional” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]The logic behind this is the same as for a single INDEX MATCH combination: the MATCH function provides the \u201ccoordinates\u201d of the desired value. This time, though, we need two MATCH functions: one to give the vertical offset, and one for the horizontal offset. <\/p>\n
The best part is that I can type any student\u2019s name and subject into cells B12 and B13 and immediately get the corresponding test score.[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” theme_builder_area=”post_content”][et_pb_column _builder_version=”4.27.2″ _module_preset=”default” type=”4_4″ theme_builder_area=”post_content”][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” 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=”50460″ theme_builder_area=”post_content”]![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\")
<\/p>\n
![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\n
Automate data transfers between spreadsheets
\n<\/span><\/p>\nFind out how<\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"The INDEX and MATCH functions in Excel are both useful for a variety of purposes. But when you combine the two functions, they create an extremely powerful tool to search arrays of data and return their desired results. The INDEX and MATCH combination is essentially a more powerful and flexible version of the VLOOKUP function, […]<\/p>\n","protected":false},"author":40,"featured_media":30416,"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,48,28],"class_list":["post-30295","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/30295","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=30295"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/30295\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/30416"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=30295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=30295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=30295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Add-on-sm_sheets-connected-new-connection.webp\")
<\/p>\n