{"id":713,"date":"2019-09-06T07:00:58","date_gmt":"2019-09-06T07:00:58","guid":{"rendered":"http:\/\/99excel.com\/home\/?p=713"},"modified":"2021-05-14T16:07:53","modified_gmt":"2021-05-14T16:07:53","slug":"xlookup-excel-formula-new-function-better-than-vlookup","status":"publish","type":"post","link":"https:\/\/99excel.com\/home\/xlookup-excel-formula-new-function-better-than-vlookup\/","title":{"rendered":"XLOOKUP Excel Formula New Function better than vlookup"},"content":{"rendered":"\n<p>New excel XLOOKUP formula has been added in updated 365 office. This is an advanced version of vlookup and have made some practical experience which were collected from vlookup history. <\/p>\n\n\n\n<p>In this formula number of column series not required, even true and false option is not required as by default it will false means exact.      <\/p>\n\n\n\n<p>99Excel.Com also have added this formula in our Advanced and Basic excel training course program for our student.<\/p>\n\n\n\n<p>In next few days, we also make education learning video on this to better learning getting.<\/p>\n\n\n\n<iframe loading=\"lazy\" width=\"560\" height=\"315\" src=\"https:\/\/www.youtube.com\/embed\/iJG_T2U1RMk\" frameborder=\"0\" allow=\"accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen=\"\"><\/iframe>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/25529a29-8f61-4a71-92c0-f5d7b280641e.jpg\" alt=\"Example of the XLOOKUP function used to return an Employee Name and Department based on Employee ID. The formula is =XLOOKUP(B2,B5:B14,C5:C14).\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">XLOOKUP<\/h2>\n\n\n\n<p>In summary, it is designed to return a related value, just like VLOOKUP \u2026 but it addresses some of the limitations of VLOOKUP and provides many additional options. Here is a screenshot of the Insert Function dialog which provides a description (and proof it exists):<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20190903a.png\" alt=\"\" class=\"wp-image-11284\"\/><\/figure>\n\n\n\n<p><em>Note: at the time I\u2019m writing this, XLOOKUP is available in O365 w\/Insiders Fast update channel. Depending on your version of Excel, you may not see it in your function list.<\/em><\/p>\n\n\n\n<p>Let\u2019s dig into the arguments:<\/p>\n\n\n\n<p>=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])<\/p>\n\n\n\n<p><strong>Where:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>lookup_value<\/strong>&nbsp;\u2013 value to find (same as VLOOKUP)<\/li><li><strong>lookup_array<\/strong>&nbsp;\u2013 lookup column (different than VLOOKUP)<\/li><li><strong>return_array<\/strong>&nbsp;\u2013 return column (different than VLOOKUP)<\/li><li><strong>[match_mode]<\/strong>&nbsp;\u2013 0 for exact match by default + more (different than VLOOKUP)<\/li><li><strong>[search_mode]<\/strong>&nbsp;\u2013 1 for first-to-last + more (different than VLOOKUP)<\/li><\/ul>\n\n\n\n<p>Let\u2019s talk about these arguments in a bit more detail.<\/p>\n\n\n\n<p><strong>Lookup_value<\/strong>&nbsp;is similar to VLOOKUP, and represents the value we are trying to find.<\/p>\n\n\n\n<p><strong>Lookup_array<\/strong>&nbsp;represents a big change from VLOOKUP. Instead of defining the entire two-dimensional range (or table_array), we can define a single column. We specifically identify the lookup column range. This means that column order doesn\u2019t matter anymore \u2026. yay! The lookup column can be anywhere in the table.<\/p>\n\n\n\n<p><strong>Return_array<\/strong>&nbsp;is another big difference from VLOOKUP. Instead of defining the return column by index number (col_index_num), we can select the return column range. This is huge because it means if we insert a new worksheet column between the lookup and return columns, nothing breaks \u2026 yay! Plus, it can be located anywhere in the table, including to the left of the lookup column&nbsp;<\/p>\n\n\n\n<p><strong>[match_mode]<\/strong>&nbsp;is an optional argument, and defaults to 0 if omitted. That means that if you don\u2019t specify it, it will perform an exact match. This is different than VLOOKUP which defaults to doing a range lookup. With VLOOKUP, we can choose True or False, but XLOOKUP offers more options:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20190903b.png\" alt=\"\" class=\"wp-image-11287\"\/><\/figure>\n\n\n\n<p>The wildcard character match is more of what we think about when we want to do a partial match. To use this option, we just add a wildcard character like asterisk (*) to the lookup value.<\/p>\n\n\n\n<p><strong>[search_mode]<\/strong>&nbsp;is an optional argument, and defaults to 1 when omitted. This replicates the behavior of VLOOKUP where it tries to find a match by starting at the top and working down.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20190903c.png\" alt=\"\" class=\"wp-image-11288\"\/><\/figure>\n\n\n\n<p>We can also search from the bottom up, which is a nice option.<\/p>\n\n\n\n<p>XLOOKUP starts with the solid foundation of VLOOKUP, and then enhances it. It is like the VLOOKUP we\u2019ve always wanted and dreamed about&nbsp;<\/p>\n\n\n\n<p>XLOOKUP will help improve our workbooks by eliminating errors and providing more flexibility. Thank you Microsoft!!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Tip to job-seekers:<\/h2>\n\n\n\n<p>Here\u2019s a tip for you if you are interviewing for a job and the interviewer asks you \u201cDo you know Excel? Well, do you know VLOOKUP?\u201d&nbsp; You answer like this: \u201cYes I know VLOOKUP \u2026 but I prefer XLOOKUP. You do know about XLOOKUP, right?\u201d \u2026 and blow their mind!!<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>Thanks<\/p>\n\n\n\n<p>99Excel.Com<\/p>\n","protected":false},"excerpt":{"rendered":"<p>New excel XLOOKUP formula has been added in updated 365 office. This is an advanced version of vlookup and have made some practical experience which were collected from vlookup history. In this formula number of column series not required, even true and false option is not required as by default it will false means exact. &#8230; <a title=\"XLOOKUP Excel Formula New Function better than vlookup\" class=\"read-more\" href=\"https:\/\/99excel.com\/home\/xlookup-excel-formula-new-function-better-than-vlookup\/\" aria-label=\"More on XLOOKUP Excel Formula New Function better than vlookup\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-713","post","type-post","status-publish","format-standard","hentry"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/713","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/comments?post=713"}],"version-history":[{"count":5,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/713\/revisions"}],"predecessor-version":[{"id":718,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/713\/revisions\/718"}],"wp:attachment":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/media?parent=713"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/categories?post=713"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/tags?post=713"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}