{"id":31791,"date":"2021-02-24T16:29:51","date_gmt":"2021-02-24T15:29:51","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=31791"},"modified":"2021-02-24T16:29:51","modified_gmt":"2021-02-24T15:29:51","slug":"match-excel","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/es\/blog\/excel-functions\/match-excel\/","title":{"rendered":"C\u00f3mo utilizar la funci\u00f3n MATCH de Excel"},"content":{"rendered":"\n[et_pb_section fb_built=&#8221;1&#8243; _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221;][et_pb_row _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221;][et_pb_column type=&#8221;4_4&#8243; _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221;][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<p><a href=\"https:\/\/support.microsoft.com\/en-us\/office\/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a\" target=\"_blank\" rel=\"noopener noreferrer\">The MATCH Excel function<\/a> tells you the position of a value within a range of cells. The function on its own can be useful in certain situations, and it can be combined with other functions for even more utility (check out the <a href=\"https:\/\/www.sheetgo.com\/blog\/excel-functions\/index-match-excel\/\">INDEX MATCH Excel<\/a> combination).<\/p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221;]<h3>Syntax<\/h3>[\/et_pb_text][et_pb_text module_class=&#8221;spreadsheet-function&#8221; _builder_version=&#8221;4.9.0&#8243; border_width_left=&#8221;4px&#8221;]=MATCH(lookup_value, lookup_array, [match_type])[\/et_pb_text][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221;]<ul>\n<li><b>lookup_value<\/b> is the value you\u2019re searching for\n<li><b>lookup_array<\/b> is the array that you want to search\n<li><b>match_type<\/b> is the type of match that you want (see below for details). If left blank, it will default to type 1 which is an approximate match<\/li>\n<\/ul>\n<h5>Match type<\/h5>\n<p>The <b>match_type<\/b> parameter has three possible values: 0, 1, and -1.<\/p>\n<ul>\n<li><b>0<\/b> specifies an exact match<\/li>\n<li><b>1<\/b> specifies an approximate match, where the value found will be equal to or less than the lookup_value. Values must be sorted in ascending order. Use an approximate match when your data may not have the exact value you\u2019re looking for<\/li>\n<li><b>-1<\/b> specifies an approximate match, where the value found will be equal to or greater than the lookup_value. Values must be sorted in descending order<\/li>\n<\/ul>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221;]<p>When using approximate matches (match_type 1 or -1) the data must be sorted in ascending (1) or descending (-1) order. If the data isn\u2019t sorted, you\u2019ll probably get an incorrect answer!<\/p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<h3>How to use the MATCH Excel function<\/h3>\n<h4>Exact match<\/h4>\n<p>I\u2019ll start with a basic example using the MATCH function to find an exact match (with match_type 0). I have a list of oceans and I want to know where in the list the Indian Ocean is situated.<\/p>\n<p>The formula here is <b>=MATCH(E2,B2:B6,0)<\/b> and the function correctly tells me that the Indian Ocean is 4th in the list.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-MATCH-exact-match.png&#8221; alt=&#8221;MATCH Excel exact match 1&#8243; title_text=&#8221;1. MATCH exact match&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<h4>Approximate match (ascending order)<\/h4>\n<p>Now I\u2019ll use <b>match_type 1<\/b> to give me an approximate match. This is useful when you know you don\u2019t have an exact match, especially when you\u2019re dealing with numerical data.<\/p>\n<p>In this example I want to get the position of the ocean with average depth closest to 3500m.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-MATCH-approximate-match-ascending.png&#8221; alt=&#8221;MATCH Excel approximate match ascending order 2&#8243; title_text=&#8221;2. MATCH approximate match ascending&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<p>The formula here is <b>=MATCH(F2,C2:C6,1)<\/b>. As mentioned before, a match_type of 1 gives an approximate match where the result is equal to or less than the lookup_value. That\u2019s why it gave me the position in the list of the Antarctic Ocean, even though the Atlantic is closer to 3500m average depth.<\/p>\n\n<p>Also remember that for this type of match, the data must be sorted in ascending order!<\/p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<h4>Approximate match (descending order)<\/h4>\n<p>The other type of approximate match is <b>match_type -1<\/b> which gives the closest value equal to or greater than the <b>lookup_value<\/b>.<\/p> <p>In this case the data must be sorted in descending order. <\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-MATCH-approximate-descending.png&#8221; alt=&#8221;MATCH Excel approximate match descending order 3&#8243; title_text=&#8221;3. MATCH approximate descending&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<p>The formula here is <b>=MATCH(F2,C2:C6,-1)<\/b>. Now you can see the difference between the two match_types \u2013 in both cases I asked for the ocean closest to 3500m depth, but got two different answers!<\/p>\n<p>This is something to keep in mind when choosing which type of match you want.<\/p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<h4>MATCH with wildcards<\/h4>\n<p>Wildcards (characters * and ?) can be used to represent any value. The asterisk (*) represents any number of any character, while the question mark (?) represents one of any character. The MATCH Excel function can use these wildcards when searching for an exact match.<\/p> <p>Pretend I wanted to find the position of the Indian Ocean in my list, but I forget how to spell it \u2013 all I know is that it starts with an \u201cI\u201d. I can use the * wildcard to represent the other characters.<\/p>\n<p>So by typing <b>=MATCH(&#8220;I*&#8221;,B2:B6,0)<\/b> the formula correctly gives me the position of the Indian Ocean!<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/4.-MATCH-with-wildcards.png&#8221; alt=&#8221;MATCH Excel wildcards 4&#8243; title_text=&#8221;4. MATCH with wildcards&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<p>You\u2019ve now seen a number of different uses of the MATCH function. Remember, it provides the position of a value in an array.<\/p>\n<p>If you want to get more use out of the MATCH function, consider combining it with the INDEX function, which can help make it even more useful by returning a value instead of just the position.<\/p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.9.0&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; sticky_enabled=&#8221;0&#8243;]<h3>Tips when using MATCH Excel<\/h3>\n<ul>\n<li>When the match_type parameter is left blank, the function defaults to an approximate match (type 1)<\/li>\n<li>Always remember to sort the data when using an approximate match. Type 1 needs data sorted in ascending order, and Type -1 in descending order<\/li>\n<li>The MATCH function is not case sensitive<\/li>\n<li>If you have duplicates in your data, the function will return the first match<\/li>\n<li>Check out the <a href=\"https:\/\/www.sheetgo.com\/blog\/excel-functions\/index-match-excel\/\">INDEX MATCH<\/a> function combination to get more out of the MATCH function<\/li>\n<\/ul>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"<p>The MATCH Excel function tells you the position of a value within a range of cells. The function on its own can be useful in certain situations, and it can be combined with other functions for even more utility (check out the INDEX MATCH Excel combination).Syntax=MATCH(lookup_value, lookup_array, [match_type]) lookup_value is the value you\u2019re searching for [&hellip;]<\/p>\n","protected":false},"author":40,"featured_media":31793,"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-31791","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\/31791","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=31791"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/31791\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/31793"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=31791"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=31791"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=31791"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}