{"id":20078,"date":"2020-10-25T23:54:42","date_gmt":"2020-10-25T22:54:42","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=20078"},"modified":"2024-03-22T18:56:05","modified_gmt":"2024-03-22T17:56:05","slug":"xlookup-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/","title":{"rendered":"How to use the XLOOKUP function in Excel with 7 Examples!"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-20302 size-full\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/xlookup-2.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"787\" height=\"277\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/xlookup-2.png 787w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/xlookup-2-300x106.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/xlookup-2-768x270.png 768w\" sizes=\"(max-width: 787px) 100vw, 787px\" \/><\/p>\n<p><em>Ever wanted to lookup values in Excel? Which of the following <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/text\/proper\/\" target=\"_blank\" rel=\"noopener\">Excel function<\/a> did you use:<\/em><\/p>\n<p><em>The dynamic one &#8211; <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/vlookup-function-introduction-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>VLOOKUP<\/strong><\/a>? The horizontal one &#8211; <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/formula\/hlookup-function-excel-introduction\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>HLOOKUP<\/strong><\/a>? The complicated one &#8211; <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-use-index-match-formula-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>INDEX MATCH<\/strong><\/a>?<\/em><\/p>\n<p>Even though the above <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Excel functions<\/a> can get the job done, they come with their own limitations. The solution to this is to use the brand new Excel function <strong>introduced in <a href=\"https:\/\/www.microsoft.com\/en-in\/microsoft-365\/buy\/compare-all-microsoft-365-products?tab=1&amp;irgwc=1&amp;OCID=AID2000142_aff_7593_1378045&amp;tduid=(ir__iefeis9zqskftzjdxmdf30kppu2xszvzei10s9pk00)(7593)(1378045)()()&amp;irclickid=_iefeis9zqskftzjdxmdf30kppu2xszvzei10s9pk00&amp;rtc=1\" target=\"_blank\" rel=\"noopener noreferrer\">Microsoft 365<\/a> &#8211; XLOOKUP!\u00a0<\/strong><\/p>\n<p>If you are using any of the older <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/what-microsoft-excel-version-do-i-have\/\" target=\"_blank\" rel=\"noopener\">versions of Excel<\/a> (2010, 2013, 2016, 2019) you won&#8217;t be able to use this function.<\/p>\n<p>XLOOKUP is a<strong> versatile and outstanding replacement<\/strong> for the above-mentioned Excel functions. It allows you to quickly lookup values in a data set (just like VLOOKUP Excel) with additional flexibility and advantages like:<\/p>\n<ul>\n<li>It can lookup data to the <strong>right or left<\/strong> of the lookup values.<\/li>\n<li>It looks for an <strong>exact match by default<\/strong>. You do not have to enter an additional argument for it.<\/li>\n<li>It allows you to provide a custom value or text if your <strong>search result is not found<\/strong>.<\/li>\n<li>It can perform a<strong> partial match lookup using wildcards<\/strong>.<\/li>\n<li>It can search for values <strong>both horizontally and vertically<\/strong>.<\/li>\n<li>It can<strong> return a range<\/strong> instead of a single value which spills out the results.<\/li>\n<li>It allows you to<strong> find the last occurrence<\/strong> in your data.<\/li>\n<\/ul>\n<p>In this article, you will be provided a detailed guide on the following:<\/p>\n<div class=\"contents\">\n<h2>Table of Contents<\/h2>\n<ul>\n<li><a href=\"#xlookup-an-introduction\"><strong>XLOOKUP &#8211; An Introduction<\/strong><\/a><\/li>\n<li><a href=\"#how-to-use-xlookup-in-excel\"><strong>How to use XLOOKUP in Excel?<\/strong><\/a><\/li>\n<li><a href=\"#approximate-match\"><strong>Approximate Match<\/strong><\/a><\/li>\n<li><a href=\"#horizontal-lookup\"><strong>Horizontal Lookup<\/strong><\/a><\/li>\n<li><a href=\"#return-a-range-instead-of-value\"><strong>Return a Range instead of Value<\/strong><\/a><\/li>\n<li><a href=\"#using-wildcards\"><strong>Using Wildcards<\/strong><\/a><\/li>\n<li><a href=\"#search-from-bottom-to-top\"><strong>Search from bottom to top<\/strong><\/a><\/li>\n<\/ul>\n<\/div>\n<p>Let&#8217;s look at each one of these points in detail!<\/p>\n<p>&nbsp;<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/#XLOOKUP_%E2%80%93_An_Introduction\" title=\"XLOOKUP &#8211; An Introduction\">XLOOKUP &#8211; An Introduction<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/#How_to_use_XLOOKUP_in_Excel\" title=\"How to use XLOOKUP in Excel?\">How to use XLOOKUP in Excel?<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/#Approximate_Match\" title=\"Approximate Match\">Approximate Match<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/#Horizontal_Lookup\" title=\"Horizontal Lookup\">Horizontal Lookup<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/#Return_a_Range_instead_of_Value\" title=\"Return a Range instead of Value\">Return a Range instead of Value<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/#Using_Wildcards\" title=\"Using Wildcards\">Using Wildcards<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/#Search_from_bottom_to_top\" title=\"Search from bottom to top\">Search from bottom to top<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-function-in-excel\/#Conclusion\" title=\"Conclusion\">Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"XLOOKUP_%E2%80%93_An_Introduction\"><\/span><strong><a id=\"xlookup-an-introduction\"><\/a>XLOOKUP &#8211; An Introduction<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><em><strong>What does it do?<\/strong><\/em><\/p>\n<p>Excel XLOOKUP can be used to<strong> search an array for a specific value<\/strong> and returns the value in the same row from another array.<\/p>\n<ul>\n<li>It can search the value both <strong>horizontally and vertically<\/strong>,<\/li>\n<li>Perform an <strong>exact or approximate match<\/strong>,<\/li>\n<li>Use <strong>wildcards<\/strong>,<\/li>\n<li>Return a <strong>custom text when no result<\/strong> is found,<\/li>\n<li><strong>Doesn&#8217;t even have the restriction of the return array to be on the right<\/strong> of the lookup array.<\/li>\n<\/ul>\n<p>Isn&#8217;t that AMAZING? It is a <strong>power-packed function<\/strong> with so many advantages!<\/p>\n<p>&nbsp;<\/p>\n<p><em><strong>Formula breakdown:<\/strong><\/em><\/p>\n<p><em>=XLOOKUP (<span style=\"color: #3366ff\">lookup_value<\/span>, <span style=\"color: #339966\">lookup_array<\/span>, <span style=\"color: #ff9900\">return_array<\/span>, [if_not_found], [match_mode], [search_mode])<\/em><\/p>\n<p>where:<\/p>\n<ul>\n<li><span style=\"color: #3366ff\">lookup_value <\/span><span style=\"color: #000000\">&#8211; the value you want to search;<\/span><\/li>\n<li><span style=\"color: #339966\">lookup_array<\/span><span style=\"color: #000000\"> &#8211; the range or array where you want to search the value;<\/span><\/li>\n<li><span style=\"color: #ff9900\">return_array<\/span><span style=\"color: #000000\"> &#8211; the range or array from which you want the result;<\/span><\/li>\n<li><em>[if_not_found]<\/em> &#8211; the value you want to display if there are no results found;<\/li>\n<li><em> [match_mode]<\/em>\n<ul>\n<li><strong>0<\/strong> &#8211; Exact Match (if no result found, then error)<\/li>\n<li><strong>-1<\/strong> &#8211; Exact or next smaller (if no result found, then the next smaller value will be displayed)<\/li>\n<li><strong>1<\/strong> &#8211; Exact or next larger (if no result found, then the next larger value will be displayed)<\/li>\n<li><strong>2<\/strong> &#8211; Wildcards<\/li>\n<\/ul>\n<\/li>\n<li><em>[search_mode]<\/em><em>\u00a0<\/em>\n<ul>\n<li><strong>1<\/strong> &#8211; to search from first<\/li>\n<li><strong>-1<\/strong> &#8211; to search from last<\/li>\n<li><strong>2<\/strong> &#8211; binary search ascending<\/li>\n<li><strong>-2<\/strong> &#8211; binary search descending<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Follow the<strong> step-by-step tutorial<\/strong> below and make sure to download the Excel workbook to follow along:<\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Xlookup-Function-in-Excel.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Vlookup_Intro1.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">Xlookup-Function-in-Excel.xlsx<\/span><\/a><\/p>\n<\/div>\n<p>Now that you are comfortable with the<strong> syntax of this incredible function<\/strong>, let&#8217;s<strong> dive into a few examples<\/strong> to understand how to use Excel XLOOKUP!<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"How_to_use_XLOOKUP_in_Excel\"><\/span><strong><a id=\"how-to-use-xlookup-in-excel\"><\/a>How to use XLOOKUP in Excel?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In this example below, there are two tables:<\/p>\n<ul>\n<li><strong>Stocklist<\/strong> containing the product&#8217;s SKU, name, price, and cost.<\/li>\n<li><strong>Orders Table<\/strong> with its quantity mentioned<\/li>\n<\/ul>\n<p>You want to <strong>extract the price of the products from the stock list<\/strong> table using XLOOKUP.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-884.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-20325 size-full\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-884.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"976\" height=\"258\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-884.png 976w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-884-300x79.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-884-768x203.png 768w\" sizes=\"(max-width: 976px) 100vw, 976px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #339966\"><strong>STEP 1:<\/strong><\/span> We need to enter the <strong>XLOOKUP<\/strong> function in a blank cell<\/p>\n<h3>=XLOOKUP(<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-886.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-20326 size-full\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-886.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"991\" height=\"314\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-886.png 991w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-886-300x95.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-886-768x243.png 768w\" sizes=\"(max-width: 991px) 100vw, 991px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #339966\"><strong>STEP 2:<\/strong><\/span>Enter the first <strong>XLOOKUP<\/strong> argument &#8211; <em>Lookup_value (product&#8217;s SKU that you are looking for)<\/em><\/p>\n<h3>=XLOOKUP(<span style=\"color: #339966\">H10<\/span>,<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-894.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20334\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-894.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"978\" height=\"314\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-894.png 978w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-894-300x96.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-894-768x247.png 768w\" sizes=\"(max-width: 978px) 100vw, 978px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #339966\"><strong>STEP 3: <\/strong><\/span>Enter the second <strong>XLOOKUP<\/strong> argument &#8211; <em>Lookup_array (the array that contains all product SKUs)<\/em><\/p>\n<h3>=XLOOKUP(H10,<span style=\"color: #339966\">$E$9:$E$12<\/span><\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-888.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20329\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-888.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"990\" height=\"314\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-888.png 990w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-888-300x95.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-888-768x244.png 768w\" sizes=\"(max-width: 990px) 100vw, 990px\" \/><\/a><\/p>\n<p><em>Ensure that you press\u00a0<strong>F4<\/strong> so that you can lock the table range<\/em><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #339966\"><strong>STEP 4: <\/strong><\/span>Enter the third <strong>XLOOKUP<\/strong> argument &#8211; <em>return_array (the array that contains price)<\/em><\/p>\n<h3>=XLOOKUP(H10,$E$9:$E$12,<span style=\"color: #339966\">$C$9:$C$12<\/span>)<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-889.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20332\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-889.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"1018\" height=\"312\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-889.png 1018w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-889-300x92.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-889-768x235.png 768w\" sizes=\"(max-width: 1018px) 100vw, 1018px\" \/><\/a><\/p>\n<p>As you will see,<strong> Excel has pulled the price of the SKU<em> LP1411 <\/em><\/strong>from the stock list and provided the result ($185) in the cell.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-891.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20335\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-891.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"992\" height=\"314\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-891.png 992w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-891-300x95.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-891-768x243.png 768w\" sizes=\"(max-width: 992px) 100vw, 992px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Apply the same <\/strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/highlight-all-excel-formula-cells\/\" target=\"_blank\" rel=\"noopener\">formula to the rest of the cells<\/a> by dragging the lower right corner downwards.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-893.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20336\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-893.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"1034\" height=\"300\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-893.png 1034w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-893-300x87.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-893-1024x297.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-893-768x223.png 768w\" sizes=\"(max-width: 1034px) 100vw, 1034px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>What if you are trying to<strong> search for a product name that is not available in the stock list<\/strong> table?<\/p>\n<p>Excel will provide you with an <strong>error<\/strong>!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-913.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20378\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-913.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"897\" height=\"269\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-913.png 897w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-913-300x90.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-913-768x230.png 768w\" sizes=\"(max-width: 897px) 100vw, 897px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Instead of showing this error, you can <strong>add a <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/highlight-custom-text-using-macros-in-excel\/\" target=\"_blank\" rel=\"noopener\">custom text<\/a><\/strong> (say, No Product Found) to display!<\/p>\n<p>To do that, simply can<strong> add the fourth optional argument of XLOOKUP function &#8211; [if_not_found]<\/strong><\/p>\n<h3>=XLOOKUP(H10,$E$9:$E$12,$C$9:$C$12,<span style=\"color: #339966\">&#8220;No Product Found&#8221;<\/span>)<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-897.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20338\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-897.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"983\" height=\"314\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-897.png 983w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-897-300x96.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-897-768x245.png 768w\" sizes=\"(max-width: 983px) 100vw, 983px\" \/><\/a><\/p>\n<p>This was a basic example of how to use XLOOKUP in Excel. Let&#8217;s<strong> explore the advanced uses of this function<\/strong> in detail!<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Approximate_Match\"><\/span><strong><a id=\"approximate-match\"><\/a>Approximate Match<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In this example, Excel will <strong>look for the income entered<\/strong> in cell F14 and<strong> find the matching tax rate<\/strong> from column C.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-836.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20213\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-836.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"849\" height=\"452\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-836.png 849w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-836-300x160.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-836-768x409.png 768w\" sizes=\"(max-width: 849px) 100vw, 849px\" \/><\/a><\/p>\n<p>Instead of looking for an exact match, it will now <strong>look for an approximate match<\/strong> i.e. if an exact match is not found it will <strong>look for the next smaller or larger item<\/strong> based on the input provided.<\/p>\n<p>If the <strong>income is greater than or equal<\/strong> to $0, the tax rate will be 13%. Similarly, if the income is greater than or equal to $8,456, the tax rate will be 18%, and so on.<\/p>\n<p>&nbsp;<\/p>\n<p>So, let&#8217;s <strong>use this function to determine the tax rate for the income amount<\/strong> mentioned in cell F14.<\/p>\n<p><em>=XLOOKUP (<span style=\"color: #3366ff\">lookup_value<\/span>, <span style=\"color: #339966\">lookup_array<\/span>, <span style=\"color: #ff9900\">return_array<\/span>, [if_not_found], [match_mode], [search_mode])<\/em><\/p>\n<p><em>Here, the<strong> three permanent arguments and one optional argument [match_mode]<\/strong> will be used. You can ignore arguments &#8211; [if_not_found] and [search_mode] for this example.<\/em><\/p>\n<p>&nbsp;<\/p>\n<p>Follow the step-by-step tutorial below to <strong>perform an approximate match<\/strong> using the XLOOKUP function:<\/p>\n<p><span style=\"color: #339966\"><strong>STEP 1:<\/strong><\/span> Enter the first argument<strong> lookup_value<\/strong> i.e.\u00a0the<span style=\"color: #000000\"> income amount mentioned in cell E5.<\/span><\/p>\n<h3>=XLOOKUP(<span style=\"color: #339966\">E5<\/span><\/h3>\n<p><span style=\"color: #000000\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-856.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20249\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-856.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"933\" height=\"397\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-856.png 933w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-856-300x128.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-856-768x327.png 768w\" sizes=\"(max-width: 933px) 100vw, 933px\" \/><\/a><br \/>\n<\/span><\/p>\n<p><span style=\"color: #339966\"><strong>STEP 2:<\/strong><\/span>Enter the lookup_array i.e. the <strong>range containing income range<\/strong> (B5: B10).<\/p>\n<h3>=XLOOKUP(E5,<span style=\"color: #339966\">B5:B10<\/span><\/h3>\n<h3><span style=\"color: #000000\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-857.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20250\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-857.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"905\" height=\"398\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-857.png 905w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-857-300x132.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-857-768x338.png 768w\" sizes=\"(max-width: 905px) 100vw, 905px\" \/><\/a><br \/>\n<\/span><\/h3>\n<p><span style=\"color: #339966\"><strong>STEP 3:<\/strong><\/span>Enter the return_array<\/p>\n<h3>=XLOOKUP(E5,B5:B10,<span style=\"color: #339966\">C5:C10<\/span><\/h3>\n<h3><span style=\"color: #000000\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-858.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20251\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-858.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"911\" height=\"397\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-858.png 911w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-858-300x131.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-858-768x335.png 768w\" sizes=\"(max-width: 911px) 100vw, 911px\" \/><\/a><br \/>\n<\/span><\/h3>\n<p><span style=\"color: #339966\"><strong>STEP 4: <\/strong><\/span>Enter the 5th argument<strong>[match_mode].\u00a0<\/strong>The accepted values for this argument are:<\/p>\n<ul>\n<li><strong>0<\/strong> &#8211; Exact Match (if no result found, then error)<\/li>\n<li><strong>-1<\/strong> &#8211; Exact or next smaller (if no result found, then the next smaller value will be displayed)<\/li>\n<li><strong>1<\/strong> &#8211; Exact or next larger (if no result found, then the next larger value will be displayed)<\/li>\n<li><strong>2<\/strong> &#8211; Wildcards<\/li>\n<\/ul>\n<p>In this example, the value<strong> will be -1.<\/strong><\/p>\n<h3>=XLOOKUP(E5,B5:B10,C5:C10,,<span style=\"color: #339966\">-1<\/span>)<\/h3>\n<p><span style=\"color: #000000\"><em>(Ignore the 4<sup>th<\/sup> argument)\u00a0<\/em><\/span><\/p>\n<h2><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/XLOOKUP-12.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-25113\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/XLOOKUP-12.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"624\" height=\"421\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/XLOOKUP-12.png 624w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/XLOOKUP-12-300x202.png 300w\" sizes=\"(max-width: 624px) 100vw, 624px\" \/><\/a><\/h2>\n<p>Below is the <strong>formula<\/strong> that should be used:<\/p>\n<h3>=XLOOKUP(E5,B5:B10,C5:C10,,-1)<\/h3>\n<p>As you know this function will perform an exact match by default, you need to<strong> use the optional part of the function &#8211; [match_mode]<\/strong>. So, if Excel fails to find an exact match, it will <strong>look for the next smaller income range<\/strong> mentioned in the table.<\/p>\n<p>&nbsp;<\/p>\n<p>One of the <strong>advantages<\/strong> of using this function over <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/vlookup-approximate-match-in-excel\/\" target=\"_blank\" rel=\"noopener\">Excel VLOOKUP for an approximate match<\/a> is that <strong>you do not need to sort the data in ascending order. <\/strong>Excel will do that on its own!<\/p>\n<p>In the example below, you will see that the data is not arranged in ascending order.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/XLOOKUP-13.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-25114\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/XLOOKUP-13.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"630\" height=\"424\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/XLOOKUP-13.png 630w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/XLOOKUP-13-300x202.png 300w\" sizes=\"(max-width: 630px) 100vw, 630px\" \/><\/a><\/p>\n<p>You can use the same formula in <strong>randomly arranged data<\/strong> and Excel will <strong>provide you with the same (correct) result<\/strong>.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Horizontal_Lookup\"><\/span><strong><a id=\"horizontal-lookup\"><\/a>Horizontal Lookup<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The <strong>syntax for performing a horizontal lookup using XLOOKUP is the same as for vertical lookup<\/strong>.<\/p>\n<p>You just need to <strong>provide Excel with the lookup and return an array<\/strong>, the table&#8217;s orientation is irrelevant to the XLOOKUP function.<\/p>\n<p>In this example, the product name is displayed on Row 5 and the price is displayed on Row 6. You need to perform a<strong> horizontal lookup<\/strong> to get your results.<\/p>\n<p><span style=\"color: #339966\"><strong>STEP 1:<\/strong><\/span>Enter the lookup value i.e. the product name mentioned in cell H6.<\/p>\n<h3>=XLOOKUP(<span style=\"color: #339966\">H6<\/span><\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-866.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20255\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-866.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"879\" height=\"228\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-866.png 879w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-866-300x78.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-866-768x199.png 768w\" sizes=\"(max-width: 879px) 100vw, 879px\" \/><\/a><\/p>\n<p><span style=\"color: #339966\"><strong>STEP 2:<\/strong><\/span>Enter the lookup array i.e. the array containing the product name.<\/p>\n<h3>=XLOOKUP(H6,<span style=\"color: #339966\">$C$5:$F$5<\/span><\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-867.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20256\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-867.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"876\" height=\"216\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-867.png 876w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-867-300x74.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-867-768x189.png 768w\" sizes=\"(max-width: 876px) 100vw, 876px\" \/><\/a><\/p>\n<p><span style=\"color: #339966\"><strong>STEP 3:<\/strong><\/span>Enter the return array i.e. the array containing prices of the product.<\/p>\n<h3>=XLOOKUP(H6,$C$5:$F$5,<span style=\"color: #339966\">$C$6:$F$6<\/span>)<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-839.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20217\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-839.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"770\" height=\"248\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-839.png 770w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-839-300x97.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-839-768x247.png 768w\" sizes=\"(max-width: 770px) 100vw, 770px\" \/><\/a><\/p>\n<p>It&#8217;s that easy to do a horizontal lookup using <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xmatch-function-2\/\" target=\"_blank\" rel=\"noopener\">the new XLOOKUP function in<\/a> Excel!<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Return_a_Range_instead_of_Value\"><\/span><strong><a id=\"return-a-range-instead-of-value\"><\/a>Return a Range instead of Value<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Excel XLOOKUP has the ability to <strong>return multiple values instead of just one<\/strong> for a correct match. It can be done <strong>without making any change in the syntax<\/strong>, simply<strong> input the entire range<\/strong> in the function instead of just a single column or row.<\/p>\n<p>In this example, we want to <strong>retrieve all the details related to the product name<\/strong> mentioned in cell G5.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-841.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20219\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-841.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"945\" height=\"323\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-841.png 945w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-841-300x103.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-841-768x263.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #339966\"><strong>STEP 1:<\/strong><\/span> Select the cell containing the lookup value.<\/p>\n<h3>=XLOOKUP(<span style=\"color: #339966\">G5<\/span><\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-875.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20260\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-875.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"835\" height=\"254\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-875.png 835w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-875-300x91.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-875-768x234.png 768w\" sizes=\"(max-width: 835px) 100vw, 835px\" \/><\/a><\/p>\n<p><span style=\"color: #339966\"><strong>STEP 2:<\/strong><\/span> Select the range containing the product list.<\/p>\n<h3>=XLOOKUP(G5,<span style=\"color: #339966\">$B$4:$B$7<\/span><\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-876.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20261\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-876.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"852\" height=\"254\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-876.png 852w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-876-300x89.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-876-768x229.png 768w\" sizes=\"(max-width: 852px) 100vw, 852px\" \/><\/a><\/p>\n<p><span style=\"color: #339966\"><strong>STEP 3:<\/strong><\/span> Select the entire range containing columns for SKU, price, and cost.<\/p>\n<h3>=XLOOKUP(G5,$B$4:$B$7,<span style=\"color: #339966\">$C$4:$E$7<\/span>)<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-844.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20220\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-844.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"959\" height=\"319\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-844.png 959w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-844-300x100.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-844-768x255.png 768w\" sizes=\"(max-width: 959px) 100vw, 959px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Excel will extract or &#8220;spill&#8221; all the values with the help of the XLOOKUP formula!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-843.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20221\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-843.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"879\" height=\"254\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-843.png 879w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-843-300x87.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-843-768x222.png 768w\" sizes=\"(max-width: 879px) 100vw, 879px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Using_Wildcards\"><\/span><strong><a id=\"using-wildcards\"><\/a>Using Wildcards<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Xlookup in Excel allows you to search for a partial match <strong>using wildcards<\/strong> characters like: <strong>* ?<\/strong> <strong>~<\/strong><\/p>\n<p>This can be done using Excel VLOOKUP as well. But the problem arises when you are actually trying to search the wildcard character itself in the data.<\/p>\n<p>For XLOOKUP, you can use wildcards in the<strong> lookup_value argument<\/strong> and specify that in the<strong> [match_mode] argument<\/strong>. Enter<strong> 2 as the match_mode<\/strong> value to let Excel know that you are trying to do a partial match.<\/p>\n<p>The three wildcards in Excel are:<\/p>\n<ul>\n<li><strong>Asterisk mark (*)<\/strong> represents any number of characters. For example, Jo* could mean Joanne, John, Joe, etc.<\/li>\n<li><strong>Question mark (?)<\/strong> represents one character. For example, Jo?n could mean John, Joan, etc.<\/li>\n<li><strong>Tilde (~)<\/strong> can be used when you want the asterisk or question mark to not be a wildcard. Simply place a tilde just before * or ?.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>In these <strong>examples below<\/strong>, you can use wildcard characters (* or ?) to search for a partial match and return the corresponding email address.<\/p>\n<p><strong>Joa*<\/strong> will search for the first match starting with Joa and any number of characters after\u00a0that <strong>(Joan) <\/strong>and return the corresponding email address <strong>(joan_ferguson@gmail.com)<\/strong>.<\/p>\n<h3>=XLOOKUP(E5,$B$4:$B$10,$C$4:$C$10,,2)<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-903.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20340\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-903.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"947\" height=\"431\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-903.png 947w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-903-300x137.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-903-768x350.png 768w\" sizes=\"(max-width: 947px) 100vw, 947px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Jo?n<\/strong> will search for the first match starting with <strong>Jo<\/strong> followed by <strong>one character<\/strong> and then <strong>n<\/strong> (John)<strong>\u00a0<\/strong>and return the corresponding email address <strong>(john_doe@gmail.com)<\/strong>.<\/p>\n<h3>=XLOOKUP(E6,$B$4:$B$10,$C$4:$C$10,,2)<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-904.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20341\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-904.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"952\" height=\"438\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-904.png 952w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-904-300x138.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-904-768x353.png 768w\" sizes=\"(max-width: 952px) 100vw, 952px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Search_from_bottom_to_top\"><\/span><strong><a id=\"search-from-bottom-to-top\"><\/a>Search from bottom to top<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In this example, we will<strong> search the value from bottom to top<\/strong> instead of the default direction (top to bottom). This can be achieved by<strong> using the 6th argument of the function<\/strong> &#8211; <em>[search_mode]<\/em>.<\/p>\n<p>It is an amazing feature if you wish to <strong>find the last occurrence of something in your data<\/strong>. Let&#8217;s dive into this XLOOKUP example to learn how.<\/p>\n<p>In this <strong>example<\/strong>, you have sales data with the Sales date, Product name and Price mentioned. You have to <strong>find out the latest price at which a particular product<\/strong> was sold.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-850.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20227\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-850.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"823\" height=\"471\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-850.png 823w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-850-300x172.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-850-768x440.png 768w\" sizes=\"(max-width: 823px) 100vw, 823px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Follow the step-by-step tutorial to search value from bottom to top:<\/p>\n<p><span style=\"color: #339966\"><strong>STEP 1: <\/strong><\/span>Enter the lookup value i.e. the product name mentioned in cell F5.<\/p>\n<h3>=XLOOKUP(<span style=\"color: #339966\">F5<\/span><\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-871.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20257\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-871.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"822\" height=\"366\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-871.png 822w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-871-300x134.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-871-768x342.png 768w\" sizes=\"(max-width: 822px) 100vw, 822px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #339966\">STEP 2:<\/span><\/strong>Enter the lookup array i.e. the array containing the product name.<\/p>\n<h3>=XLOOKUP(F5,<span style=\"color: #339966\">C4:C30<\/span><\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-872.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20258\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-872.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"823\" height=\"411\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-872.png 823w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-872-300x150.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-872-768x384.png 768w\" sizes=\"(max-width: 823px) 100vw, 823px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #339966\">STEP 3:<\/span><\/strong>Enter the return array i.e. the array containing prices of the product.<\/p>\n<h3>=XLOOKUP(F5,C4:C30,<span style=\"color: #339966\">D4:D30<\/span><\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-873.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20259\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-873.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"823\" height=\"455\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-873.png 823w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-873-300x166.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-873-768x425.png 768w\" sizes=\"(max-width: 823px) 100vw, 823px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #339966\">STEP 4:<\/span><\/strong>Enter the <strong>6th argument [search_mode]<\/strong>. It <strong>will be -1<\/strong> for this example as you want to search from bottom to top.<\/p>\n<h3>=XLOOKUP(F5,C4:C30,D4:D30,,,<span style=\"color: #339966\">-1<\/span>)<\/h3>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-851.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-20228\" title=\"How to use the XLOOKUP function in Excel with 7 Examples!\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-851.png\" alt=\"How to use the XLOOKUP function in Excel with 7 Examples!\" width=\"718\" height=\"516\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-851.png 718w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/10\/Screenshot-851-300x216.png 300w\" sizes=\"(max-width: 718px) 100vw, 718px\" \/><\/a><\/p>\n<p>Excel will <strong>start the search from the bottom<\/strong> and the first match will be produced as a result!<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>XLOOKUP is the latest and <strong>outstanding addition to the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\">Excel lookup family<\/a><\/strong>. It can search for a value and return the matching result, similar to the Excel VLOOKUP.<\/p>\n<p><em>But that&#8217;s not it!<\/em><\/p>\n<p>There are <strong>several reasons why this new XLOOKUP function is better<\/strong>:<\/p>\n<ul>\n<li>It can lookup data to the right or left of the lookup values.<\/li>\n<li>It looks for an exact match by default. You do not have to enter an additional argument for it.<\/li>\n<li>It allows you to provide a custom value or text if your search result is not found.<\/li>\n<li>It can perform a partial match lookup using wildcards.<\/li>\n<li>It can search for values both horizontally and vertically.<\/li>\n<li>It can return a range instead of a single value which spills out the results.<\/li>\n<li>It allows you to find the last occurrence in your data.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>This AWESOME function is available for Microsoft 365 users only. <a href=\"https:\/\/www.microsoft.com\/en-nz\/microsoft-365\/excel?rtc=1\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>Click here<\/strong><\/a> to purchase it! You can also go through the Microsoft tutorial on XLOOKUP by <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929#:~:text=The%20XLOOKUP%20function%20searches%20a,the%20closest%20(approximate)%20match.\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><strong>clicking here<\/strong><\/a>!<\/p>\n<p><strong>Further Learning:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-vs-vlookup\/\" target=\"_blank\" rel=\"noopener\">XLOOKUP vs VLOOKUP: Top 5 Comparison of Excel Lookup Functions<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-with-multiple-criteria\/\" target=\"_blank\" rel=\"noopener\">2 Easy Methods on How to Use XLOOKUP with Multiple Criteria<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-in-excel\/\" target=\"_blank\" rel=\"noopener\">Top 11 Examples of Using XLOOKUP in Excel &#8211; The Ultimate Guide<\/a><\/li>\n<\/ul>\n<p><strong>Make sure to download our FREE PDF on the<a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"> 333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n<p><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"nofollow noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-14329 size-medium\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg\" alt=\"Microsoft Excel Training\" width=\"300\" height=\"200\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-1024x682.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-768x512.jpg 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups.jpg 1340w\" sizes=\"(max-width: 300px) 100vw, 300px\" title=\"\"><\/a><\/p>\n<p>You can learn more about how to use Excel by viewing our FREE Excel webinar training on <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-43.html\" target=\"_blank\" rel=\"noopener noreferrer\">Formulas<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-5.html\" target=\"_blank\" rel=\"noopener noreferrer\">Pivot Tables<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/power-query-data-cleansing-webinar.html\" target=\"_blank\">Power Query<\/a>,<\/strong> and <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-39.html\" target=\"_blank\" rel=\"noopener noreferrer\">Macros &amp; VBA<\/a><\/strong>!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ever wanted to lookup values in Excel? Which of the following Excel function did you use: The dynamic one &#8211; VLOOKUP? The horizontal one &#8211; HLOOKUP? The complicated one &#8211; INDEX MATCH? Even though the above Excel functions can get the job done, they come with their own limitations. The solution to this is to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":20360,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"How to use the XLOOKUP function in Excel with 7 Examples!","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[703,3,11],"tags":[696,79,44,694,695],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/20078"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=20078"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/20078\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/20360"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=20078"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=20078"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=20078"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}