{"id":4201,"date":"2025-11-30T15:53:20","date_gmt":"2025-11-30T14:53:20","guid":{"rendered":"https:\/\/sandshine.eu\/?p=4201"},"modified":"2025-12-16T13:13:27","modified_gmt":"2025-12-16T12:13:27","slug":"xlookup-function-in-excel-complete-tutorial-with-examples","status":"publish","type":"post","link":"https:\/\/sandshine.eu\/index.php\/2025\/11\/30\/xlookup-function-in-excel-complete-tutorial-with-examples\/","title":{"rendered":"XLOOKUP Function in Excel: Complete Tutorial with Examples"},"content":{"rendered":"\n<p>Still counting columns for VLOOKUP? There&#8217;s a better way. XLOOKUP fixes the annoying parts of its predecessor and adds features that actually make sense.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">What Makes XLOOKUP Different<\/h5>\n\n\n\n<p>VLOOKUP forces you to organize data in a specific way. XLOOKUP doesn&#8217;t:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Look left or right<\/strong> &#8211; Your columns can be in any order<\/li>\n\n\n\n<li><strong>No column counting<\/strong> &#8211; Point directly at what you need<\/li>\n\n\n\n<li><strong>Built-in error handling<\/strong> &#8211; Show custom messages instead of #N\/A<\/li>\n\n\n\n<li><strong>Multiple search modes<\/strong> &#8211; Exact matches, approximate matches, or wildcards<\/li>\n\n\n\n<li><strong>Search direction control<\/strong> &#8211; Start from top or bottom<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">The Syntax<\/h5>\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>You only need the first three parts. The rest are optional.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-07c9e02\" data-block-id=\"07c9e02\"><style>.stk-07c9e02 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 1: Basic Product Lookup<\/h5>\n\n\n\n<p>You have a product list and need to find a price:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product Code<\/th><th>Product Name<\/th><th>Price<\/th><\/tr><\/thead><tbody><tr><td>P001<\/td><td>Wireless Mouse<\/td><td>$29.99<\/td><\/tr><tr><td>P002<\/td><td>USB Keyboard<\/td><td>$45.50<\/td><\/tr><tr><td>P003<\/td><td>Monitor Stand<\/td><td>$67.25<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=XLOOKUP(\"P002\", A2:A4, C2:C4)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: $45.50<\/p>\n\n\n\n<p>Find &#8220;P002&#8221; in column A, return the matching value from column C. No column numbers needed.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-5838952\" data-block-id=\"5838952\"><style>.stk-5838952 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 2: Looking Left<\/h5>\n\n\n\n<p>Your price list has prices first, then product codes:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Price<\/th><th>Product Name<\/th><th>Product Code<\/th><\/tr><\/thead><tbody><tr><td>$29.99<\/td><td>Wireless Mouse<\/td><td>P001<\/td><\/tr><tr><td>$45.50<\/td><td>USB Keyboard<\/td><td>P002<\/td><\/tr><tr><td>$67.25<\/td><td>Monitor Stand<\/td><td>P003<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=XLOOKUP(\"P002\", C2:C4, A2:A4)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: $45.50<\/p>\n\n\n\n<p>VLOOKUP can&#8217;t do this. XLOOKUP doesn&#8217;t care where your columns are.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-a986a14\" data-block-id=\"a986a14\"><style>.stk-a986a14 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 3: Custom Error Messages<\/h5>\n\n\n\n<p>What if the product doesn&#8217;t exist?<\/p>\n\n\n\n<p><strong>Formula<\/strong>: <code>=XLOOKUP(\"P999\", A2:A4, C2:C4, \"Product not found\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: &#8220;Product not found&#8221;<\/p>\n\n\n\n<p>The fourth argument lets you show whatever message you want instead of an error.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-3500070\" data-block-id=\"3500070\"><style>.stk-3500070 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 4: Tiered Lookups<\/h5>\n\n\n\n<p>Sales commission structure:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Sales Threshold<\/th><th>Commission Rate<\/th><\/tr><\/thead><tbody><tr><td>$0<\/td><td>5%<\/td><\/tr><tr><td>$10,000<\/td><td>7%<\/td><\/tr><tr><td>$25,000<\/td><td>10%<\/td><\/tr><tr><td>$50,000<\/td><td>12%<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Find the commission rate for $32,000 in sales:<\/p>\n\n\n\n<p><strong>Formula<\/strong>: <code>=XLOOKUP(32000, A2:A5, B2:B5, , -1)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 10%<\/p>\n\n\n\n<p>The <code>1<\/code> tells XLOOKUP to find an exact match or the next smallest value. Great for pricing tiers, tax brackets, or any stepped structure. Leave the fourth argument empty by using two commas.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-2c3fab5\" data-block-id=\"2c3fab5\"><style>.stk-2c3fab5 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 5: Partial Text Matching<\/h5>\n\n\n\n<p>Customer list:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Customer Name<\/th><th>Account ID<\/th><\/tr><\/thead><tbody><tr><td>Smith Corp<\/td><td>ACC-1001<\/td><\/tr><tr><td>Johnson Industries<\/td><td>ACC-1002<\/td><\/tr><tr><td>Williams Group<\/td><td>ACC-1003<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=XLOOKUP(\"*Johnson*\", A2:A4, B2:B4, \"Not found\", 2)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: ACC-1002<\/p>\n\n\n\n<p>The <code>2<\/code> in match_mode turns on wildcard matching. Use * for multiple characters or ? for single characters.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-a9edb1c\" data-block-id=\"a9edb1c\"><style>.stk-a9edb1c {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 6: Search Backwards<\/h5>\n\n\n\n<p>Transaction log with duplicate IDs:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Date<\/th><th>Transaction ID<\/th><th>Amount<\/th><\/tr><\/thead><tbody><tr><td>1\/15<\/td><td>TXN-5001<\/td><td>$250<\/td><\/tr><tr><td>1\/22<\/td><td>TXN-5001<\/td><td>$175<\/td><\/tr><tr><td>1\/29<\/td><td>TXN-5001<\/td><td>$300<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=XLOOKUP(\"TXN-5001\", B2:B4, C2:C4, , 0, -1)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: $300<\/p>\n\n\n\n<p>The <code>-1<\/code> in search_mode tells XLOOKUP to start from the bottom and work up. You get the most recent transaction.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-78b6c33\" data-block-id=\"78b6c33\"><style>.stk-78b6c33 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Match Modes (Fifth Argument)<\/h5>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>0<\/strong> (default): Exact match only<\/li>\n\n\n\n<li><strong>1<\/strong>: Exact match or next smallest (for sorted data)<\/li>\n\n\n\n<li><strong>-1<\/strong>: Exact match or next largest (for sorted data)<\/li>\n\n\n\n<li><strong>2<\/strong>: Wildcard match<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Search Modes (Sixth Argument)<\/h5>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>1<\/strong> (default): Search first to last<\/li>\n\n\n\n<li><strong>-1<\/strong>: Search last to first<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Start Using It<\/h5>\n\n\n\n<p>Open a blank spreadsheet. Set up a simple table. Try each example above. You&#8217;ll get comfortable with XLOOKUP faster than you think.<\/p>\n\n\n\n<p>Once you start using it, you&#8217;ll forget about VLOOKUP.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p><em>Questions about XLOOKUP? Want to see it applied to your specific work? Let&#8217;s connect.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>XLOOKUP fixes everything annoying about VLOOKUP. Look left or right, handle errors gracefully, and skip the column counting.<\/p>\n","protected":false},"author":1,"featured_media":4842,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[35,36,37],"tags":[1027,1021,1019,1026,1008,1012,1016,1005,1007,1011,1024,1015,1018,1017,1003,1020,1010,1014,1004,1009,1023,1013,1022,1006,1025],"class_list":["post-4201","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-applications","category-excel","category-learning","tag-advanced-excel-functions","tag-excel-365-xlookup","tag-excel-data-lookup","tag-excel-formulas-tutorial","tag-excel-lookup-functions","tag-excel-tutorial-2025","tag-excel-xlookup-how-to","tag-excel-xlookup-tutorial","tag-how-to-use-xlookup","tag-learn-xlookup","tag-replace-vlookup-with-xlookup","tag-what-is-xlookup","tag-xlookup-approximate-match","tag-xlookup-error-handling","tag-xlookup-examples","tag-xlookup-for-beginners","tag-xlookup-formula","tag-xlookup-function-examples","tag-xlookup-function-in-excel","tag-xlookup-guide","tag-xlookup-match-modes","tag-xlookup-syntax","tag-xlookup-tips","tag-xlookup-vs-vlookup","tag-xlookup-wildcard-search"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"_links":{"self":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4201","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/comments?post=4201"}],"version-history":[{"count":3,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4201\/revisions"}],"predecessor-version":[{"id":5010,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4201\/revisions\/5010"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media\/4842"}],"wp:attachment":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media?parent=4201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/categories?post=4201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/tags?post=4201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}