{"id":3812,"date":"2021-12-06T16:36:02","date_gmt":"2021-12-07T00:36:02","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3812"},"modified":"2021-12-06T16:36:05","modified_gmt":"2021-12-07T00:36:05","slug":"what-is-the-xlookup-function-in-excel-how-to-use-it","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3812","title":{"rendered":"What Is the XLOOKUP Function in Excel? How to Use It?"},"content":{"rendered":"\n<p>The XLOOKUP function is useful when you need to find something in a table or range. Learn how to use the XLOOKUP function in Excel.<\/p>\n\n\n\n<p>If you use Excel a lot, you have probably used LOOKUP functions quite often. If you&#8217;re not familiar, the LOOKUP function is used to search through a single row or column to find a specific value in another row or column.<\/p>\n\n\n\n<p>This function comes in handy when working with a lot of data in an Excel sheet. In Excel, there are several types of LOOKUP functions.<\/p>\n\n\n\n<p>In this article, we will tell you everything you need to know about the XLOOKUP function in Microsoft Excel, along with some helpful examples.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"what-is-xlookup-function-in-excel\">What Is XLOOKUP Function in Excel?<\/h2>\n\n\n\n<p>XLOOKUP replaces the old functions like HLOOKUP, VLOOKUP, and LOOKUP. It supports vertical and horizontal lookups.<\/p>\n\n\n\n<p>In other words, the XLOOKUP function allows you to quickly find a value in a given dataset, both horizontally and vertically, and returns its corresponding value in a different row or column.<\/p>\n\n\n\n<p>There are many ways to use the XLOOKUP function to get the most out of it. We&#8217;ll take a look at some of the XLOOKUP examples that would give you a better understanding of it.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"how-to-access-xlookup-function\">How to Access XLOOKUP Function<\/h3>\n\n\n\n<p>Unfortunately, XLOOKUP is not available for users running Office 2010, 2013, 2016, and 2019 versions. It is only available on the Microsoft Office 365 suite.<\/p>\n\n\n\n<p>If you&#8217;re not using Microsoft 365 suite on your computer, you might need to\u00a0<a href=\"https:\/\/www.microsoft.com\/microsoft-365\/buy\/compare-all-microsoft-365-products\" rel=\"noreferrer noopener\" target=\"_blank\">upgrade to Microsoft 365<\/a>\u00a0to get access to the XLOOKUP function. And if you are already using Microsoft 365, then you will find the option already enabled in Excel. XLOOKUP is also available on\u00a0<a href=\"https:\/\/www.office.com\/\" rel=\"noreferrer noopener\" target=\"_blank\">Office 365 Online<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"syntax-for-xlookup-function\">Syntax for XLOOKUP Function<\/h3>\n\n\n\n<p>XLOOKUP function&#8217;s syntax is similar to that of VLOOKUP and HLOOKUP. If you have ever used them, you&#8217;ll find using XLOOKUP more convenient. Here&#8217;s the syntax for the XLOOKUP function in Excel:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=XLOOKUP(lookup_value, lookup_array, return_array, &#91;if_not_found], &#91;match_mode], &#91;search_mode])<\/code><\/pre>\n\n\n\n<p>XLOOKUP function supports up to six arguments, and here are their values.<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li><strong>lookup_value (required):<\/strong>&nbsp;the value that you want to find.<\/li><li><strong>lookup_array (required):<\/strong>&nbsp;the array where you want to look for the lookup value.<\/li><li><strong>return_array (required):<\/strong>&nbsp;the array from where you want to fetch and return the values when the lookup value is found.<\/li><li><strong>[if_not_found] (optional):<\/strong>&nbsp;When the match isn&#8217;t found, this is the value to be returned.<\/li><li><strong>[match_mode] (optional):<\/strong>&nbsp;This argument lets you specify the type of match you&#8217;re looking after. There are different values to specify it:<ul><li><strong>0<\/strong>&nbsp;&#8211; It looks for an exact match, and the value should exactly match the value in the lookup_array. It is also set as default when not mentioned.<\/li><li><strong>-1<\/strong>&nbsp;&#8211; It looks for the exact match and, once found, returns to the next smaller value.<\/li><li><strong>1<\/strong>&nbsp;&#8211; It looks for the exact match and once found returns to the next larger value.<\/li><li><strong>2<\/strong>&nbsp;&#8211; It does partial matching using wildcards where&nbsp;<strong>*<\/strong>,&nbsp;<strong>?<\/strong>, and&nbsp;<strong>~<\/strong>&nbsp;have special meaning.<\/li><\/ul><\/li><li><strong>[search_mode] (optional):<\/strong>&nbsp;Used to specify the XLOOKUP&#8217;s search mode in lookup_array. There are different values to specify the same:<ul><li><strong>1<\/strong>&nbsp;&#8211; Performs search starting from the first item. It&#8217;s set as default when nothing is specified.<\/li><li><strong>-1<\/strong>&nbsp;&#8211; Performs reverse search starting at the last item.<\/li><li><strong>2<\/strong>&nbsp;&#8211; Performs a binary search in the lookup_array where the data needs sorting in ascending order. If the data isn&#8217;t sorted, it may produce errors or wrong results.<\/li><li><strong>-2<\/strong>&nbsp;&#8211; Performs a binary search in the lookup_array where the data needs sorting in descending order. If the data isn&#8217;t sorted, it may produce errors or wrong results.<\/li><\/ul><\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"advantages-and-disadvantages-of-xlookup-function-in-excel\">Advantages and Disadvantages of XLOOKUP Function in Excel<\/h2>\n\n\n\n<p>XLOOKUP is still a function that has its advantages over VLOOKUP and INDEX\/MATCH. But it has its disadvantages too.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"advantages-of-xlookup-function\">Advantages of XLOOKUP Function<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li>It works both vertically and horizontally.<\/li><li>Needs three arguments, instead of four arguments in the VLOOKUP and INDEX MATCH functions.<\/li><li>Always defaults to an exact match.<\/li><li>Can perform partial match lookups using wildcards.<\/li><li>Can perform lookups in descending order.<\/li><li>Uses one function instead of two in INDEX MATCH.<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"disadvantages-of-xlookup-function\">Disadvantages of XLOOKUP Function<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li>Optional arguments may look complex to beginners.<\/li><li>Can consume more time when selecting two ranges, and there are too many cells in the spreadsheet.<\/li><li>Returns an error when lookup and array returns are not of the same length.<\/li><li>Need to remember both lookup and return ranges.<\/li><\/ul>\n\n\n\n<p><strong><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"how-to-use-the-xlookup-function-in-excel\">How to Use the XLOOKUP Function in Excel<\/h2>\n\n\n\n<p>The XLOOKUP function works similarly to the LOOKUP function in Excel. You can use XLOOKUP by simply selecting cell references where the function should operate.<\/p>\n\n\n\n<p>Alternatively, you can also use the \u201cFormula bar\u201d box located at the top; and insert the XLOOKUP function syntax there.<\/p>\n\n\n\n<p><strong>Example 1&nbsp;<\/strong>&nbsp;&nbsp;&nbsp;uses XLOOKUP to look up a country name in a range, and then return its telephone country code. It includes the&nbsp;<strong>lookup_value<\/strong>&nbsp;(cell F2),&nbsp;<strong>lookup_array<\/strong>&nbsp;(range B2:B11), and&nbsp;<strong>return_array<\/strong>&nbsp;(range D2:D11) arguments. It doesn&#8217;t include the&nbsp;<strong>match_mode<\/strong>&nbsp;argument, as XLOOKUP produces an exact match by default.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/25529a29-8f61-4a71-92c0-f5d7b280641e.jpg\" alt=\"Example of the XLOOKUP function used to return an Employee Name and Department based on Employee ID. The formula is =XLOOKUP(B2,B5:B14,C5:C14).\"\/><\/figure>\n\n\n\n<p><strong>Note:\u00a0<\/strong>XLOOKUP uses a lookup array\u00a0and a return array, whereas VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula, in this case, would be:\u00a0<strong>=VLOOKUP(F2,B2:D11,3,FALSE)<\/strong><\/p>\n\n\n\n<p>\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<\/p>\n\n\n\n<p><strong>Example 2&nbsp;<\/strong>&nbsp;&nbsp;&nbsp;looks up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP can return an array with multiple items, so&nbsp;a single formula can return both employee name and department from cells C5:D14.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/9bdc7843-ee8e-4815-833f-64cff5080775.jpg\" alt=\"Example of the XLOOKUP function used to return an Employee Name and Department based on Employee IDt. The formula is: =XLOOKUP(B2,B5:B14,C5:D14,0,1)\"\/><\/figure>\n\n\n\n<p>\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<\/p>\n\n\n\n<p><strong>Example 3&nbsp;<\/strong>&nbsp;&nbsp;&nbsp;adds an&nbsp;<strong>if_not_found<\/strong>&nbsp;argument to the preceding example.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/1e91c8d7-6130-4936-a8f5-018b15403898.jpg\" alt=\"Example of the XLOOKUP function used to return an Employee Name and Department based on Employee ID with the if_not_found argument. The formula is =XLOOKUP(B2,B5:B14,C5:D14,0,1,&quot;Employee not found&quot;)\"\/><\/figure>\n\n\n\n<p>\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<\/p>\n\n\n\n<p><strong>Example 4&nbsp;<\/strong>&nbsp;&nbsp;&nbsp;looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It sets the&nbsp;<strong>if_not_found<\/strong>&nbsp;argument to return&nbsp;0&nbsp;(zero) if nothing is found. The&nbsp;<strong>match_mode<\/strong>&nbsp;argument is set to&nbsp;1, which means the function will look for an exact match, and if it can&#8217;t find one, it returns the next larger item. Finally, the&nbsp;<strong>search_mode<\/strong>&nbsp;argument is set to&nbsp;<strong>1<\/strong>, which means the function will search from the first item to the last.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/d1b2c44f-adec-472f-8f68-44a58a3f4bfe.jpg\" alt=\"Image of the XLOOKUP function used to return a tax rate based on maximum income. This is an approximate match.The formula is: =XLOOKUP(E2,C2:C7,B2:B7,1,1)\"\/><\/figure>\n\n\n\n<p><strong>Note:\u00a0<\/strong>XARRAY&#8217;s\u00a0<strong>lookup_array<\/strong>\u00a0column is to the right of the\u00a0<strong>return_array<\/strong>\u00a0column, whereas VLOOKUP can only look from left-to-right.<\/p>\n\n\n\n<p>Ref: https:\/\/support.microsoft.com\/en-us\/office\/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The XLOOKUP function is useful when you need to find something in a table or range. Learn how to use the XLOOKUP function in Excel. If you use Excel a lot, you have probably used LOOKUP functions quite often. If you&#8217;re not familiar, the LOOKUP function is used to search through a single row or <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3812\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[973],"class_list":["post-3812","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-xlookup"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3812","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3812"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3812\/revisions"}],"predecessor-version":[{"id":3813,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3812\/revisions\/3813"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3812"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3812"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3812"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}