{"id":6758,"date":"2023-06-19T22:27:31","date_gmt":"2023-06-19T22:27:31","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=6758"},"modified":"2024-04-26T11:21:56","modified_gmt":"2024-04-26T11:21:56","slug":"how-to-use-xlookup-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-use-xlookup-in-excel\/","title":{"rendered":"How to use XLOOKUP in Excel?"},"content":{"rendered":"<p>XLOOKUP in Excel revolutionizes the way users search and retrieve data within spreadsheets, offering unmatched flexibility and efficiency. This dynamic function allows users to search horizontally or vertically, find exact matches or approximate matches, and retrieve corresponding values effortlessly. Whether you&#8217;re navigating large datasets, creating interactive dashboards, or performing complex data analysis, XLOOKUP in Excel streamlines the process with its intuitive syntax and powerful capabilities. Say goodbye to cumbersome VLOOKUP and HLOOKUP functions and embrace the future of Excel functionality with XLOOKUP. With XLOOKUP in Excel, users can navigate through data with ease, making informed decisions faster than ever before.<\/p>\n<p><strong>This Content Covers:<\/strong><\/p>\n<ol>\n<li><a href=\"#what-is-xlookup?\"><strong>What is XLOOKUP?<\/strong><\/a><\/li>\n<li><a href=\"#xlookup-function-syntax,-purpose,-and-arguments\"><strong> XLOOKUP Function Syntax, Purpose, and Arguments<\/strong><\/a><\/li>\n<li><a href=\"#how-to-fetch-a-lookup-value-using-xlookup?\"><strong> How to <\/strong><strong>Fetch a Lookup Value<\/strong><strong> using XLOOKUP?<\/strong><\/a><\/li>\n<li><a href=\"#how-to-look-up-and-fetch-an-entire-record-using-xlookup?\"><strong> How to <\/strong><strong>Look-up and Fetch an Entire Record<\/strong><strong> using XLOOKUP?<\/strong><\/a><\/li>\n<li><a href=\"#how-to-do-a-two-way-look-up-using-xlookup?\"><strong> How to do a Two-way Look-up using XLOOKUP?<\/strong><\/a><\/li>\n<li><a href=\"#error-handling-using-xlookup.\"><strong> Error Handling using XLOOKUP.<\/strong><\/a><\/li>\n<li><a href=\"#how-to-use-xlookup-function-to-look-up-in-multiple-ranges?\"><strong> How to use XLOOKUP Function to Look-up in Multiple Ranges?<\/strong><\/a><\/li>\n<li><a href=\"#how-to-find-tax-rate\/commission-rate-using-xlookup?\"><strong> How to Find Tax Rate\/Commission Rate using XLOOKUP?<\/strong><\/a><\/li>\n<li><a href=\"#how-to-do-conditional-lookup-using-xlookup-and -other-functions-in-excel?\"><strong> How to do Conditional Lookup using XLOOKUP and Other Functions in Excel?<\/strong><\/a>\n<ul>\n<li><strong>XLOOKUP with MAX and MIN Functions<\/strong><\/li>\n<li><strong>XLOOKUP with COUNTIF Function<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<h2><strong id=\"what-is-xlookup?\">1. What is XLOOKUP?<\/strong><\/h2>\n<p>The XLOOKUP is a built-in function in Microsoft Excel which is only available in Office 365. This is an upgraded version of VLOOKUP or HLOOKUP. With the help of this function, you can identify a value in a horizontal or vertical dataset and obtain the value that corresponds to it in some other columns or rows.<\/p>\n<h2><strong id=\"xlookup-function-syntax,-purpose,-and-arguments\">2. XLOOKUP Function Syntax, Purpose, and Arguments<\/strong><\/h2>\n<p><strong>Syntax:<\/strong> =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])<\/p>\n<p><strong>Purpose: <\/strong>The purpose of this function is to lookup values in range or array<\/p>\n<p><strong>Arguments: <\/strong>There are three compulsory and three optional arguments in this function,<\/p>\n<p><strong>lookup_value-<\/strong> The value to look for.<\/p>\n<p><strong>lookup_array-<\/strong> The array or range in which we are looking for the value.<\/p>\n<p><strong>return_array-<\/strong> The array or range to return.<\/p>\n<p><strong>[if_not_found] (optional)-<\/strong> If the lookup value cannot be found, this value will be returned. An #N\/A error will be returned if you don\u2019t specify\u00a0this argument.<\/p>\n<p><strong>[match_mode] (optional)-<\/strong> Here you can specify the type of match you want,<\/p>\n<p>0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.<\/p>\n<p><strong>[search_mode] (optional)-<\/strong> Here you specify how the XLOOKUP function should search the lookup_array,<\/p>\n<p>1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.<\/p>\n<h2><strong id=\"how-to-fetch-a-lookup-value-using-xlookup?\">3. How to <\/strong><strong>Fetch a Lookup Value<\/strong><strong> using XLOOKUP?<\/strong><\/h2>\n<p>Suppose we have this dataset, and we want to get the sales amount for Fiona (the lookup value)<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter wp-image-6759 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-1.png\" alt=\"XLOOKUP in Excel\" width=\"525\" height=\"304\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-1.png 525w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-1-300x174.png 300w\" sizes=\"(max-width: 525px) 100vw, 525px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Type or copy the lookup value in another cell.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-6760 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-2.png\" alt=\"XLOOKUP in Excel\" width=\"584\" height=\"218\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-2.png 584w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-2-300x112.png 300w\" sizes=\"(max-width: 584px) 100vw, 584px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Insert the following formula in the cell where you would like to get the result.<\/p>\n<p><strong>=XLOOKUP(F2,B2:B9,D2:D9)<\/strong><\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-6763 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-3.png\" alt=\"XLOOKUP in Excel\" width=\"590\" height=\"219\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-3.png 590w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-3-300x111.png 300w\" sizes=\"(max-width: 590px) 100vw, 590px\" \/><\/p>\n<p><strong>Step 3: <\/strong>Press Enter key to get the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6764 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-4.png\" alt=\"XLOOKUP in Excel\" width=\"599\" height=\"228\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-4.png 599w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-4-300x114.png 300w\" sizes=\"(max-width: 599px) 100vw, 599px\" \/><\/p>\n<h2><strong id=\"how-to-look-up-and-fetch-an-entire-record-using-xlookup?\">4. How to <\/strong><strong>Look-up and Fetch an Entire Record<\/strong><strong> using XLOOKUP?<\/strong><\/h2>\n<p>Suppose you want to get the entire record for the sales-rep whose id is YZX701.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6765 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-5.png\" alt=\"XLOOKUP in Excel\" width=\"618\" height=\"200\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-5.png 618w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-5-300x97.png 300w\" sizes=\"(max-width: 618px) 100vw, 618px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Select the cell where you want the result to be displayed and insert the below given formula inside the cell.<\/p>\n<p><strong>=XLOOKUP(F2,A2:A9,B2:D9)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6766 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-6.png\" alt=\"XLOOKUP in Excel\" width=\"631\" height=\"205\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-6.png 631w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-6-300x97.png 300w\" sizes=\"(max-width: 631px) 100vw, 631px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Hit the Enter button to get the corresponding result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6767 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-7.png\" alt=\"XLOOKUP in Excel\" width=\"637\" height=\"194\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-7.png 637w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-7-300x91.png 300w\" sizes=\"(max-width: 637px) 100vw, 637px\" \/><\/p>\n<h2><strong id=\"how-to-do-a-two-way-look-up-using-xlookup?\">5. How to do a Two-way Look-up using XLOOKUP?<\/strong><\/h2>\n<p>Suppose we want to know which shop\/store the sales rep whose id no is YZX701 is assigned to. We can do this by using the first method that we have seen. But here we will see how we can use the two-way look-up to get the result and what benefit it gives us.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6768 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-8.png\" alt=\"XLOOKUP in Excel\" width=\"594\" height=\"232\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-8.png 594w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-8-300x117.png 300w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Insert this formula in G2.<\/p>\n<p><strong>=XLOOKUP(G1,B1:D1,XLOOKUP(F2,A2:A9,B2:D9))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6769 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-9.png\" alt=\"XLOOKUP in Excel\" width=\"607\" height=\"202\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-9.png 607w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-9-300x100.png 300w\" sizes=\"(max-width: 607px) 100vw, 607px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Hit Enter to get the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6770 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-10.png\" alt=\"XLOOKUP in Excel\" width=\"614\" height=\"224\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-10.png 614w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-10-300x109.png 300w\" sizes=\"(max-width: 614px) 100vw, 614px\" \/><\/p>\n<p><strong>Step 3: <\/strong>The benefit of using two-way lookup is that if you change the title of G1, the result will change too. Suppose you want to know the name of this sales rep, all you have to do is change the title of G1 from Assigned To, to Sales Rep.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6771 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-11.png\" alt=\"XLOOKUP in Excel\" width=\"623\" height=\"229\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-11.png 623w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-11-300x110.png 300w\" sizes=\"(max-width: 623px) 100vw, 623px\" \/><\/p>\n<h2><strong id=\"error-handling-using-xlookup.\">6. Error Handling using XLOOKUP.<\/strong><\/h2>\n<p>Here we were searching for the total sales amount for the sales rep <strong>Diana<\/strong> which resulted in <strong>#N\/A<\/strong> error. This happened because there is no sales rep named Diana on our list. While working with large datasets, we might find this error quite often. With the help of XLOOKUP function we can set our custom error text that the function should return if a value can\u2019t be found.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6772 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-12.png\" alt=\"XLOOKUP in Excel\" width=\"638\" height=\"234\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-12.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-12-300x110.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Type your custom error text inside the formula.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6773 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-13.png\" alt=\"XLOOKUP in Excel\" width=\"621\" height=\"222\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-13.png 621w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-13-300x107.png 300w\" sizes=\"(max-width: 621px) 100vw, 621px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Hit the Enter key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6774 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-14.png\" alt=\"XLOOKUP in Excel\" width=\"624\" height=\"227\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-14.png 624w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-14-300x109.png 300w\" sizes=\"(max-width: 624px) 100vw, 624px\" \/><\/p>\n<h2><strong id=\"how-to-use-xlookup-function-to-look-up-in-multiple-ranges?\">7. How to use XLOOKUP Function to Look-up in Multiple Ranges?<\/strong><\/h2>\n<p>In this worksheet we have two data tables instead of one and we don\u2019t know in which one the sales rep Diana is. Now we have to look for the sales amount for Diana by looking up both of these ranges together by using a <strong>Nested LOOKUP Formula.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6775 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-15.png\" alt=\"XLOOKUP in Excel\" width=\"627\" height=\"238\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-15.png 627w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-15-300x114.png 300w\" sizes=\"(max-width: 627px) 100vw, 627px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Insert this nested <strong>XLOOKUP<\/strong> formula in B12.<\/p>\n<p><strong>=XLOOKUP(A12,B2:B9,D2:D9,XLOOKUP(A12,G2:G9,I2:I9))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6776 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-16.png\" alt=\"XLOOKUP in Excel\" width=\"589\" height=\"228\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-16.png 589w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-16-300x116.png 300w\" sizes=\"(max-width: 589px) 100vw, 589px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Press Enter key to get the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6777 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-17.png\" alt=\"XLOOKUP in Excel\" width=\"595\" height=\"217\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-17.png 595w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-17-300x109.png 300w\" sizes=\"(max-width: 595px) 100vw, 595px\" \/><\/p>\n<h2><strong id=\"how-to-find-tax-rate\/commission-rate-using-xlookup?\">8. How to Find Tax Rate\/Commission Rate using XLOOKUP?<\/strong><\/h2>\n<p>Suppose we have this datasheet here and on the right-side table we have the sales amount for which the commission will be given and the commission rate.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6778 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-18.png\" alt=\"XLOOKUP in Excel\" width=\"604\" height=\"203\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-18.png 604w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-18-300x101.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Select cell E2 and insert the following formula in this cell. Press Enter key to get the Commission.<\/p>\n<p><strong>=XLOOKUP(D2,$G$2:$G$7,$H$2:$H$7,0,-1)*D2<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6779 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-19.png\" alt=\"XLOOKUP in Excel\" width=\"637\" height=\"212\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-19.png 637w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-19-300x100.png 300w\" sizes=\"(max-width: 637px) 100vw, 637px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Now drag the fill-handle down to apply the formula on the other cells of this column. The formula here takes the data in D2 as the lookup value and looks through the lookup table on the right. Here we have used -1 as the fifth argument inside the formula which means that it will look for an exact match, and if it doesn\u2019t find one, it will return the value just smaller than the lookup value. In the commission column cell E3 is empty because the total sales amount of D3 (lookup value) is lower than the lowest sales amount for which the commission will be given. So, this person will not get any commission.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6780 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-20.png\" alt=\"XLOOKUP in Excel\" width=\"638\" height=\"213\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-20.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-20-300x100.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<h2><strong id=\"how-to-do-conditional-lookup-using-xlookup-and\">9. How to do Conditional Lookup using XLOOKUP and Other Functions in Excel?<\/strong><\/h2>\n<h3><strong>9.1 XLOOKUP with MAX and MIN Functions<\/strong><\/h3>\n<p>Suppose we have this datasheet, and we want to know who the best and worst sellers are based on their total sales.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6781 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-21.png\" alt=\"XLOOKUP in Excel\" width=\"592\" height=\"345\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-21.png 592w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-21-300x175.png 300w\" sizes=\"(max-width: 592px) 100vw, 592px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Insert the below given formula inside cell B11.<\/p>\n<p><strong>=XLOOKUP(MAX(XLOOKUP(D1,$D$1,$D$2:$D$9)),XLOOKUP(D1,$D$1,$D$2:$D$9),$B$2:$B$9)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6782 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-22.png\" alt=\"XLOOKUP in Excel\" width=\"602\" height=\"392\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-22.png 602w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-22-300x195.png 300w\" sizes=\"(max-width: 602px) 100vw, 602px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Press Enter to get the best seller. The formula has returned Elgar as the best seller. From the Total Sales column we can see that he has the highest total sales, which is $51857.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6783 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-23.png\" alt=\"XLOOKUP in Excel\" width=\"566\" height=\"345\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-23.png 566w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-23-300x183.png 300w\" sizes=\"(max-width: 566px) 100vw, 566px\" \/><\/p>\n<p><strong>Step 3: <\/strong>Here we used <strong>MIN <\/strong>function with <strong>XLOOKUP<\/strong> function to get the worst seller which is Diana who has the lowest total sales of $22964.<\/p>\n<p><strong>=XLOOKUP(MIN(XLOOKUP(D1,$D$1,$D$2:$D$9)),XLOOKUP(D1,$D$1,$D$2:$D$9),$B$2:$B$9)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6784 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-24.png\" alt=\"XLOOKUP in Excel\" width=\"579\" height=\"357\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-24.png 579w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-24-300x185.png 300w\" sizes=\"(max-width: 579px) 100vw, 579px\" \/><\/p>\n<h3><strong>9.2 XLOOKUP with COUNTIF Function<\/strong><\/h3>\n<p>We can use <strong>COUNTIF<\/strong> function with <strong>XLOOKUP<\/strong> to set a condition in order to know how many sellers have sold more than $30000.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6785 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-25.png\" alt=\"XLOOKUP in Excel\" width=\"546\" height=\"310\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-25.png 546w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-25-300x170.png 300w\" sizes=\"(max-width: 546px) 100vw, 546px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Select the cell where you want the result to be displayed and insert this formula.<\/p>\n<p><strong>=COUNTIF(XLOOKUP(D1,$D$1,$D$2:$D$9),&#8221;&gt;30000&#8243;)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6786 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-26.png\" alt=\"XLOOKUP in Excel\" width=\"566\" height=\"346\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-26.png 566w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-26-300x183.png 300w\" sizes=\"(max-width: 566px) 100vw, 566px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Press Enter key to get the result in that cell.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6787 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-27.png\" alt=\"XLOOKUP in Excel\" width=\"586\" height=\"333\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-27.png 586w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/XLOOKUP-27-300x170.png 300w\" sizes=\"(max-width: 586px) 100vw, 586px\" \/><\/p>\n<h2>Application of XLOOKUP in Excel<\/h2>\n<ul>\n<li><strong>Dynamic Data Retrieval<\/strong>: Utilize XLOOKUP in Excel to dynamically retrieve data from tables based on specified criteria, simplifying data analysis tasks.<\/li>\n<li><strong>Vertical and Horizontal Lookup<\/strong>: Perform both vertical and horizontal lookups with XLOOKUP, enabling flexible data retrieval across different orientations within spreadsheets.<\/li>\n<li><strong>Exact and Approximate Matches<\/strong>: Find exact matches or approximate matches with XLOOKUP, providing versatility in searching for data within Excel tables.<\/li>\n<li><strong>Handling Errors<\/strong>: XLOOKUP handles errors more efficiently compared to traditional lookup functions, reducing the likelihood of #N\/A errors and improving data integrity.<\/li>\n<li><strong>Replacing VLOOKUP and HLOOKUP<\/strong>: Transition from VLOOKUP and HLOOKUP to XLOOKUP for improved functionality and ease of use, as XLOOKUP combines the features of both.<\/li>\n<li><strong>Array-Like Behavior<\/strong>: XLOOKUP can return entire columns or rows of data as arrays, enabling more advanced calculations and data manipulation within Excel.<\/li>\n<\/ul>\n<p><strong>For ready-to-use Dashboard Templates:<\/strong><\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>XLOOKUP in Excel revolutionizes the way users search and retrieve data within spreadsheets, offering unmatched flexibility and efficiency. This dynamic function allows users to search horizontally or vertically, find exact matches or approximate matches, and retrieve corresponding values effortlessly. Whether you&#8217;re navigating large datasets, creating interactive dashboards, or performing complex data analysis, XLOOKUP in Excel &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/how-to-use-xlookup-in-excel\/\"> <span class=\"screen-reader-text\">How to use XLOOKUP in Excel?<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[788],"tags":[789,790],"class_list":["post-6758","post","type-post","status-publish","format-standard","hentry","category-xlookup-function","tag-xlookup-excel","tag-xlookup-function-excel"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/6758","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=6758"}],"version-history":[{"count":4,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/6758\/revisions"}],"predecessor-version":[{"id":9121,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/6758\/revisions\/9121"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=6758"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=6758"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=6758"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}