{"id":35325,"date":"2021-08-03T14:28:58","date_gmt":"2021-08-03T12:28:58","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=35325"},"modified":"2021-08-03T14:28:58","modified_gmt":"2021-08-03T12:28:58","slug":"recherchevexcel","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/fr\/blog\/excel-functions\/xlookup-excel\/","title":{"rendered":"Comment utiliser la fonction XLOOKUP dans Excel ?"},"content":{"rendered":"<p>[et_pb_section fb_built=&#8221;1&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_row _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_column type=&#8221;4_4&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]When analyzing large amounts of data within a table or range in Excel, it is a common practice to track certain values and find the corresponding results. <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929\" target=\"_blank\" rel=\"noopener\">Microsoft&#8217;s XLOOKUP function<\/a> in excel is one of the many functions that you can use for a range of search types of your data.<\/p>\n<p>XLOOKUP is the function you need when want to search for values by row. It allows you to select your desired range of data across multiple columns in order to find the corresponding value or values in the same row. The XLOOKUP function can also provide the closest approximate match if it fails to find the specific value you are looking for. This makes it the most powerful function of the <a href=\"https:\/\/www.sheetgo.com\/blog\/excel-functions\/\" target=\"_blank\" rel=\"noopener\">excel functions<\/a> available, due to its versatility in searches and approximation abilities.<\/p>\n<p>Let\u2019s take a look at what makes up the XLOOKUP function and how you can use this function in Excel.[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<h2>Syntax<\/h2>\n<p>The following are the two versions of XLOOKUP you can use to search for data.<\/p>\n<p>[\/et_pb_text][et_pb_text module_class=&#8221;spreadsheet-function&#8221; _builder_version=&#8221;4.16&#8243; border_width_left=&#8221;4px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>=<b>XLOOKUP(lookup_value, lookup_array, return_array)<\/b><\/p>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<ul>\n<li><b>lookup_value<\/b> is the value to search for<\/li>\n<li><b>lookup_array<\/b> is the range of data to search from<\/li>\n<li><b>return_array<\/b> is the array of data to return, in other words, the data you\u2019re looking for<\/li>\n<\/ul>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>To include approximate matches if your exact value isn\u2019t found, use the following formula instead.<\/p>\n<p>[\/et_pb_text][et_pb_text module_class=&#8221;spreadsheet-function&#8221; _builder_version=&#8221;4.16&#8243; border_width_left=&#8221;4px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>=<b>XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])<\/b><\/p>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<ul>\n<li><b>[if_not_found]<\/b> is the determined value if an exact match isn\u2019t found. For example, setting this to \u20180\u2019 means that if the formula can\u2019t find a match, it will display a \u20180\u2019 instead.<\/li>\n<li><b>[match_mode]<\/b> indicates how accurate this match is set to be.<br \/>0 = if no exact match, no alternative<br \/>-1 = if no exact match, next smallest item<br \/>1 = if no exact match, next largest item<br \/>2 = wildcard match<\/li>\n<li><b>[search_mode]<\/b> specifies the type of search to use.<br \/>1 = search starting from the first item in your table<br \/>-1 = reverse search starting from the last item in your table<br \/>2 = binary search of the <i>lookup_array<\/i> that is in ascending order. (this must be correct in order to function properly)<br \/>-2 = binary search of the <i>lookup_array<\/i> that is in descending order. (this must be correct in order to function properly)<\/li>\n<\/ul>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<h3>How to use the XLOOKUP function in Excel<\/h3>\n<p>Now let\u2019s put the above into practice. To demonstrate how the XLOOKUP function in Excel works, we will use the case study of identifying the sales and number of employees of specific retail departments.<\/p>\n<p>First, let\u2019s start with identifying this month\u2019s sales of various departments using our XLOOKUP function.<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/07\/XLOOKUP-1.png&#8221; alt=&#8221;xlookup excel 1&#8243; title_text=&#8221;XLOOKUP 1&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/07\/XLOOKUP-2.png&#8221; alt=&#8221;xlookup excel &#8211; 2&#8243; title_text=&#8221;XLOOKUP 2&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]In order to find this month\u2019s sales in the Electronics department, I have carried out the following steps:<\/p>\n<ol>\n<li>Dedicate a cell to where I want my information to be located (including the name of the department next to this).<\/li>\n<li>Start creating the function with <b>=XLOOKUP<\/b> (and selecting the \u2018Electronics\u2019 cell (E2) I created. This is the <i>lookup_value<\/i> I want to find within the table of data.<\/li>\n<li>After highlighting the <i>lookup_value<\/i>, add a comma. Then select the A column where my department data is located &#8211; this is the <i>lookup_array<\/i> in which the \u2018Electronics\u2019 department will be searched for.<\/li>\n<li>Add another comma after this <i>lookup_array<\/i>. Then highlight the column where the value I\u2019m looking for is located (column C). Finish the XLOOKUP function by adding a final bracket\u00a0&#8216; <b>)<\/b> &#8216;.<\/li>\n<li>The finished formula looks like this <b>=XLOOKUP(E2,A:A,C:C)<\/b>. Press \u2018enter\u2019 on your keyboard for the formula to find your value.<\/li>\n<\/ol>\n<p>As you can see, following these steps, the XLOOKUP formula has correctly provided this month\u2019s sales for the electronics department.<\/p>\n<p>In order to find another department\u2019s sales data, we can simply replace the text in cell E2 to another department name such as \u2018Books\u2019. The excel will automatically update with the new sales data.<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/07\/XLOOKUP-3.png&#8221; alt=&#8221;xlookup excel &#8211; 3&#8243; title_text=&#8221;XLOOKUP 3&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>We can also return multiple items of data by changing the <i>lookup_array<\/i> to cover more than one column.<\/p>\n<p>For example, if I wanted to find both the number of employees and this month\u2019s sales for the Grocery department, the XLOOKUP formula would look like this <b>=XLOOKUP(E2,A:A,B:C)<\/b>.<\/p>\n<p>After pressing \u2018enter\u2019, both values are correctly displayed.<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/07\/XLOOKUP-4.png&#8221; alt=&#8221;xlookup excel &#8211; 4&#8243; title_text=&#8221;XLOOKUP 4&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>The ability to search for values in two different columns is something that is completely unique to the XLOOKUP formula. This is why XLOOKUP is a great option for those wanting to find various values in a big data sheet with ease &#8211; it can only be done using the XLOOKUP formula. <\/p>\n<p>Finally, let\u2019s look for values using the optional XLOOKUP function to find an approximate match. For this case study, let\u2019s use another table of data and try to find the maximum number of employees needed to make a set amount of money. <\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/07\/XLOOKUP-5.png&#8221; alt=&#8221;xlookup excel &#8211; 5&#8243; title_text=&#8221;XLOOKUP 5&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/07\/XLOOKUP-6.png&#8221; alt=&#8221;xlookup excel &#8211; 6 &#8221; title_text=&#8221;XLOOKUP 6&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; width=&#8221;640px&#8221; max_width=&#8221;640px&#8221; height=&#8221;440px&#8221; max_height=&#8221;440px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>Here are the steps following to use the XLOOKUP formula to show the maximum number of employees needed to make $22,300.<\/p>\n<ol>\n<li>First, we start with the required function we have talked about in the other examples <b>=XLOOKUP(D2,B:B,A:A<\/b>.<\/li>\n<li>Following this, add another comma, and set your <i>[if_not_value]<\/i> &#8211; here I have established it as \u20180\u2019.<\/li>\n<li>Then, choose the accuracy of the match. As we are looking for the maximum number of employees, I have set this to \u20181\u2019. This should provide us with the next largest match.<\/li>\n<li>Add another comma. Now, choose the search type &#8211; we can use the normal search type here, so add \u20181\u2019. End your XLOOKUP formula with a final bracket &#8211; \u2018<b>)<\/b>\u2019.<\/li>\n<li>Your final XLOOKUP formula should look like this <b>=XLOOKUP(D2,B:B,A:A,0,1,1)<\/b>.<\/li>\n<\/ol>\n<p>As you can see, the XLOOKUP formula has provided us with the correct value for the maximum number of employees we\u2019d need to make $22,300.<\/p>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<h3>XLOOKUP in Excel<\/h3>\n<p>And there you have it! The XLOOKUP function is an extremely powerful formula that can help you carry out a variety of data searches with intuitive matching abilities.<\/p>\n<p>To find out more about other LOOKUP functions within excel, check out our articles below.<\/p>\n<p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When analyzing large amounts of data within a table or range in Excel, it is a common practice to track certain values and find the corresponding results. Microsoft&#8217;s XLOOKUP function in excel is one of the many functions that you can use for a range of search types of your data. XLOOKUP is the function [&hellip;]<\/p>\n","protected":false},"author":40,"featured_media":35414,"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-35325","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\/fr\/wp-json\/wp\/v2\/posts\/35325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=35325"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/35325\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/35414"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=35325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=35325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=35325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}