New Xlookup Function: A Vlookup Comparison
ฝัง
- เผยแพร่เมื่อ 30 มิ.ย. 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
Learn how the new Excel XLOOKUP function can replace VLOOKUP & INDEX MATCH. Partial match, reverse order, and horizontal lookup formulas are now possible with this one function.
XLOOKUP can perform both vertical or horizontal lookups to return a value from a matching row or column. The function only requires 3 arguments and separates the lookup and return arranges for lookup formulas that are easier to read and write.
Read the full article and download the Excel file: www.excelcampus.com/functions...
Here is the agenda for the video.
1. Overview of XLOOKUP. How to write the formula and an explanation of all 5 arguments. 0:56
2. Horizontal lookups 4:16
3. Partial Match with Wildcard Characters 4:56
4. Reverse Search Order 6:54
5. A brief preview of XMATCH 7:47
6. XLOOKUP vs. VLOOKUP 8:12
7. XLOOKUP to the Left 10:04
8. XLOOKUP Closest/Approximate Match 10:33
9. XLOOKUP vs INDEX MATCH 11:30
10. Pros or Advantages of XLOOKUP 13:01
11. Cons or Drawbacks of XLOOKUP 13:48
12. # VALUE! Error with XLOOKUP 14:20
13. VLOOKUP Assistant preview 16:02
14. Availability & Compatibility 18:48
15. Questions for you 19:49
Please leave a comment below and answer either or both of the following questions:
1. What do you think of XLOOKUP?
2. What other uses or problems are you wondering if XLOOKUP can solve?
Additional Resources:
To get XLOOKUP you will need to have an Office 365 subscription and be on the Insiders Fast Channel using Excel for Windows or Mac. Here is a link to join the free Office Insiders program. insider.office.com/
Download the Excel file I use in the video: www.excelcampus.com/filedownl...
Note: You will need a version of Excel that supports XLOOKUP for the formulas to work.
Download the VLOOKUP Assistant Tool: www.excelcampus.com/functions...
Video about VLOOKUP Assistant: • Excel Vlookup Assistan...
Thank you! 🙂
#ExcelCampus #MsExcel
00:00 Introduction
00:16 The New XLOOKUP
04:18 Horizontal Lookup
04:56 Partial Match with Wildcards
06:54 Search Order
08:13 XLOOKUP vs. VLOOKUP
10:34 Closest Match
11:31 XLOOKUP vs. INDEX MATCH
13:02 Pros & Cons of XLOOKUP
18:48 Availability & Compatibility
![ภาพนี้ก็ฮาเหมือนกันนะเนี้ย #1 SS8 [ พากย์นรก MEME.EXE ] | easy boy](http://i.ytimg.com/vi/Ytc50m91QK8/mqdefault.jpg)
Yes!! X-lookup is so much more logical
14:21 The easiest way to avoid the range length error is by selecting the column instead of selecting a specific range. This also makes the formula easier to type and understand. For example, =XLOOKUP(A4,E:E,G:G) is way cleaner than =XLOOKUP(A4,E4:E1003,G4:G1003), and it catches everything in both columns instead of accidently leaving any data out.
life saver!
Thank you Jon. Your videos on TH-cam and your articles on your website have helped me a lot.
Keep up the good work and God bless You :)
I feel like you have enhanced my overall intelligence by this video. Made me think some complex formulaes and scenarios in a understanding way. Thanks.
I think X-lookup is very easy to use And handy, as we're very well versed with V-lookup.
Also, I absolutely loved the tools you've created to apply various functions in a matter of just a few clicks. Great job! 👏
Thanks! I needed to see comparison of these three.
The latest xLookUp has the [Value if not found] argument which is very handy because I didn't have to add IfError just to avoid the #N/A return.
Great job Jon, thanks for the detailed explanation. Looks like it will be a nice upgrade when available.
Your awesome
No words to express my gratitude
I follow only you for excel tutorial and recommended my frds.
Thanks a lot 🤗
Love your channel. I have learned so much from you, thank you.
This is really informative Jon, now i am well aware of the cons, big thanks
Thanks for this x-loopup video and congratulations with the birth of you daughter Emily!!
Thank you Len! 🙂
Dear please keep updating new functions!!! Thanks, very useful,,,,
All your videos are so helpful. Thank you soo much!
Alt+arrow key down to display the argument options. I didn't know that! Thanks, Jon. Another insightful and useful video
Thanks Roderick! Happy to hear you learned something new. 🙂
Thank you very much for your great video, Jon! It is very helpful to us.
your videos are very easy to understand and helpful. Thanks a lot
Nicely explained. I appreciate your effort 🙂🙂
Thank you, always a great video from you.
Thank you very much for a great video with comparisons.
Thanks, Jon for your explanation much appreciated
Thanks Nissim! 🙂
Excellent. Very thorough.
Thanks Oz! 😀
v-lookup & h-lookup seem to have made their time... Thanks for the presentation!
Great video Jon. Can't wait to have them to try them out. I have office 365 insider and don't have them yet. Thanks for the video.
Thanks John! Sorry to hear you didn't get the update yet. I hope it's rolled out to everyone soon. I didn't get it initially on my main PC, but was able to get it on a virtual machine...
@@ExcelCampus Just got an update and I have the XLookup and the XMatch. This is great!!!!!
Nice lesson, will try it and learn it more
Well presented and a fab new feature. Thanks :)
Thanks Denise! 👍
Wow, i like the way you demonstrate ... Great
Great video, thank you!
Thanks Joyce! 🙂
1. Yes
2. simplify my life
Excellent video
Thanks Daniel! 🙂
Hi, Xlookup is great. I will use it for searching for a formula based on what criteria i use. Thanks
Thanks a lot for the knowledge
Thanks Ochora! 🙂
Great video!
great video! Thank you!
You are welcome, AJ! 😀
You have a soothing voice 😄
Thank you Chang! 🙂
Look forward to the multiple result return video
Thanks for the vote David! 👍
XLOOKUP makes sense, VLOOKUP is great but it can be limited. XLOOKUP gives you some not all of HLOOKUP, MATCH and INDEX in a one stop shop. I have Microsoft 356 and I have the latest updates but I haven't gotten the XLOOKUP yet.
Try this with google sheets for an alternative
th-cam.com/video/CKVTaJq3B2k/w-d-xo.html
th-cam.com/video/0B9cc7shvoI/w-d-xo.html
It appears that in a newer release of Excel they have added an additional [optional argument] to the xlookup formula after "return array", called [if not found], which now completely messes up any pre-exisiting xlookup formulas such as those in the example file. The optional arguments for match mode and search mode are now "pushed" along one step and necessitate an additional comma and/or value in the formulas in order to function.
I expect this is still considered a beta function and therefore things are likely to change until full public release.
I like xlookup and think it will be helpful for 365 users. I am still lingering on 2016, so it will be a while before I can use it regularly.
Thanks Norma! 🙂
I think Xlookup will be much easier to use and teach to new colleagues as it is far more logical, no bothering with explaining ad nauseum that you need to count including the lookup value column to the return value column, no trying to explain to end users that inserting a column or a might cause their lookup to break completely or report incorrect data and at last a single lookup function that can look to the left, of course not forgetting that if you enter an array it will spill :) The search bottom to top of list is also a great innovation and I just wish that I had had a simple match function that accepted wild cards in my previous job. All in all I am a great fan of this and the new Xmatch function.
Yes! I completely agree. Thanks Roy! 🙂
thanks very new feature. wish my employer had it. had to send a file home, turn around and use my personal laptop...
I got all excited then you tell me that I need the subscription... My work is still on Office 2013!
Yeah sorry about that. Unfortunately I believe this will be the biggest downside of XLOOKUP. It's true of most new features of Excel though. Backwards compatibility is going to be a limiting factor of XLOOKUP for many years. I know organizations are slower to update to Office 365, but hopefully that changes in the future... 🤷♂️
Great video, for me the left and right lookup will be very helpful, as found the index match a bit to complex, will save having to restructure my data sets. The wild looks like also could be helpful, but would like to understand the case when looking for variation on the end text string, for example we have to match data from different system and company names have variation like Limited v Ltd plus variation like Ltd v Ltd. Which vlookup does not work with on exact match which need to ensure correct matching..
Thanks Clive! I'll do a follow-up video on that wildcard scenario. You would likely need to extract the company name from the lookup value, then use the asterisk wildcard at the end of the value. Similar to what I showed in the video, but the asterisk goes after the value instead of before it.
You're amazing 😂
Hi Jon, this is very helpful. Just only wondering if by this time, is there any way we could add on to Excel 2109 and the earlier version of excel the xlookop?
Great video: enough depth and detail:
One thing I did not understand, however: why to the look-up and return arrays have to be absolute values? Or, is this a best practice?
Thank you so much for the Video. may I ask how to add in Vlookup Assistant?
thank you
I believe this may be what I am looking for. I am trying to pull vendors out of bank downloads with a lot of garbage in it and would like to know if this will help. Thank you for a great presentation
Hi Ronnie,
Yes, the wildcard feature of XLOOKUP should be able to help. Let's say you want to find a match for Walmart and the bank data looks like the following:
TRX1354354 - Walmart - Store #1993
You could specify wildcards in the XLOOKUP's lookup_value where the value of Walmart is in cell B4.
=XLOOKUP("*"&B4&"*",...
This will work really well if you are building a "category matching" feature in a budget or expense report file. I'll do a follow-up video on this scenario.
Thanks! 🙂
Its great! what about if we have multiple multiple criteria
Very helpful
Glad it helped! 😀
Very good
Xlookup is the GOAT
Great video, is Xlookup faster that index match when having large data sets?
Thanks Joel! Great question! I believe the performance will be about the same, but I will do a video on a performance test comparison once XLOOKUP is in production. The function is currently in a beta/preview state and it is not fully optimized for performance yet. BTW, next week's video is on a comparison between INDEX MATCH versus VLOOKUP CHOOSE for doing lookups to the left on large data sets. Stay tuned for the results... 😀
I love your videos, very helpful...However, how do I get a true 'exact' match? I am still having issues with capitalization, i.e. Salesforce Account ID #s. I've had the issue with VLOOKUP and XLOOKUP, then I applied a EXACT function to compare the columns but what a pain!
great explanition has off toyou
Hi, thanks for the video, do we have something for fuzzy lookup, or can xlookup be used for the same?
Great question Peter! XLOOKUP won't be able to do a true fuzzy lookup. This is usually like something where you have the name "Bill" and you want to find "William" as match. Microsoft does have a free add-in for fuzzy lookups though.
Hi Jon, I downloaded the VLookup Assistant Macro spreadsheet but the “Create Xlookup” Macro was not available.
She looks so cute!!!!!!CONGRATULATION!!!!!!!!!!!!!!!!!!!!!!!!
Thank you Ka! 🙂
Is there nothing easy to do a lookup on the nth value (down or up)? I was hoping XLOOKUP would do this.
1st of all thank you for the video. Suppose I have a workbook with XLOOKUP formula in it & I send it to other party like coworker or else whom doesn't have XLOOKUP formula availability in the computer then that other party can't open the workbook that I sent; is it?
Hi Jon,
Thanks for this super useful video.
For the 2D Lookup to get the value for South Region and Mar Month I have used the below approach:
XLOOKUP(C3, $B$7:$M$7,XLOOKUP(B3,$A$8:$A$11,$B$8:$M$11))
I have used Nested XLOOKUPs.
Is this correct and acceptable?
Regards,
Deepak.
Thank you Jon - great video. I'd like to know if XLOOKUP can return the next previous date (in the format 'dd/mm/yyyy') to an entered date that is not an exact match in the array?
Thanks Gail! Great question! Yes, you can specify a -1 for the match_mode argument to return an exact match or the next smallest item. I will add this to the list for a future video. The format the date is returned in is controlled by the number formatting that is applied to the cell that the formula is in. The function itself does not change the number formatting.
I hope that helps. :-)
Jon - Excellent presentation of xlookup features
Is there any method or functions, which I can search the lookupvalue against which multiple value exists? Say, for example a customer (lookup value) having multiple e-mail ids or having multiple address locations, etc. (just like one to many relationship). The result I would like to capture in either adjacent rows or columns / transpose the details. It will be of immense help to me is you please guide me.
Hi Santharam, Great question! Yes, you can use the new FILTER function for this. This is part of the Dynamic Arrays update that will hopefully be available to all Office 365 subscribers soon. Here is a video on the Dynamic Arrays and it mentions the Filter feature.
th-cam.com/video/1HF0UGMF070/w-d-xo.html
you need formula array for this if you can't use the FILTER function. There are a bunch of videos that solve this using formula array
Hi Jon. just stumbled on your account, great videos I have learned quite a bit! But I was wondering if you can help me with something? I Have been trying to use excel to track recipes and stock, Is there anyway I can have my inventory laid out, then my recipes and then use a formula to track amounts used with each recipe and keep a running total of the amount of each ingredient remaining? Thanks!
Hi Jon Can you pls explain the differences in useing between "Unique" function and "xlookup" at your earliest convenience.
UNIQUE is one of the new Dynamic Array functions. It can do 2 things:
1. If you have multiple items in a long list, UNIQUE can give you a list without any duplicates. That's a distinct list.
2. If you want all of the items in the list that appear just one time, you'd use UNIQUE and select the Unique option in the function.
XLOOKUP is totally different. It will retrieve data and match data from different datasets.
Great answer Oz!
You can also use UNIQUE to get a distinct count, by nesting it in the COUNT or COUNTA function.
UNIQUE and XLOOKUP have two different use cases. However, you could nest an XLOOKUP in a UNIQUE function to return a list of unique matches. Not sure how useful that would be though...
what about if you need to look up multiple iterations of the first reference?
Hi Jon, would like to seek your advise about wild card, if I have a long list of sub account need to map with list of master account which make the lookup value uncertain its a list. Any suggestion as its many to many condition instead of one to many..
Hi Ivan, it depends on how the account strings are structured. If the sub accounts don't share the same string or pattern as the accounts they are being mapped to, then a wildcard lookup might work. It's tough to say without seeing an example.
Can you use xlookup with a sum feature that would return the total from a table?
Will make complex formulae far more readable. I wonder when they will introduce a multi-line indentable and commentable formula editor, as they have with powerquery M code?
I hope we see that in the future too. It's been mentioned on the Excel Uservoice Site several times. Here is a link to one of the posts you can vote for. excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10589244-add-real-multiline-editing-with-auto-indentation-t
@@ExcelCampus thanks - upvote done
Hi, I have a company excel license so I can not download the tool you created to convert vlookup to xlookup do you have a video on how to do that?
Thank you
Hi Jon, I really like xlookup, in fact I have several times decided I should write a macro to do just that. Now I don''t need to. I'd like to try it, but I do not seem to have the option to join Insider channel. Is if available on Office 365 Pro?
Thanks Andrea! Yes, Insiders Fast is available on Office 365 Pro. There are special instructions to install it with the Office Deployment tool. Here is a link to those instructions. Make sure to read the FAQ section where it mentions changing the Channel setting to "InsiderFast". This one gets me every time!
insider.office.com/en-us/business/deploy/windows/office-deployment-tool
I hope that helps.
Hi Jon, Thanks. After a stumble or two (including the one you warned me of), I got it installed and found it to be perfect to compare two lists that might not exactly match. Just what I've been looking for. And your video makes a lot of really good points. Thanks again.
Funny how they added the if not returned part to this ;)
You forgot a few other benefits (when you listed them):
1) Can look to the right and the left
2) If you insert or delete a column it will still work correctly
3) if will use less memory
4) copying the formula to another column will not necessarily require modificatoin
Great points! Thanks Daniel! 🙂👍
Hi,I was going thru your XLOOKUP tutor however in my excel under XLOOKUP function [if_not_found] command is available. What should I do for this command. Can I ignore this and move forward for match mode and search mode? If yes please guide me. Below is the function display
=XLOOKUP(Lookup_Value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
Q1. yes
I need help figuring out how to write a formula. I have a long list of assets (20k rows) which are located at 120 diff addresses. Each address has it's own unique location code as well (addresses in 3 columns - street, city, state,....and the corresponding location code in a 4th column). One another tab I wish to create a simplified spreadsheet that just pulls just the location code, address city state....so that I'll end up with a worksheet that just has 120 rows (plus a header of course). Help!
since you made this another criteria has been added - [if_not_found]. Does this field work the same as an if/iferror statement?
pretty much
Every demo I’ve seen of XLOOKUP either states you must use F4 to get absolute references to the search / return arrays or, alternatively, select the entire column E:E - the latter I would think may be slower performance wise (although easier and maybe more consistent to avoid the out of range selection issue due to blank cells in the source data).
Does anyone know WHY we need to use absolute references with XLOOKUP? This seems the be the single largest hurdle in getting others on my team to use it or understand it.
Hey Keifer, Great question! The absolute references are NOT required. However, if you copy the formula down then you will want to create absolute references (with F4) so the range references don't change as you copy the formula down. You can avoid absolute references by using Tables and structured reference formulas. However, this might be an equally challenging hurdle for your team if they are not familiar with Tables yet.
In regards to whole column references (E:E), I try to avoid those whenever possible because they can lead to inaccurate results if values are entered or pasted below the lookup and return ranges.
So, if you're using XLOOKUP or any other lookup formula with regular references, then it is a good practice to hit the F4 key after selecting the ranges. This is true for VLOOKUP and INDEX MATCH too. Not just XLOOKUP
I hope that helps. Thanks again and have a nice day! 🙂
I could be wrong, but didn't vlookup have a limitation on how many rows it could lookup? I remember hitting an error all the time when I had an inventory report of more than 65,000 rows. I got around it by starting another vlookup range at the 64900 row mark.
Does xlookup have the same limitation?
Hi Det,
That was a limitation of the number of rows on the sheet back in Excel 2003. Starting in Excel 2007 the number of rows was increased to over 1 million. VLOOKUP itself did not have limitations. However, if you were opening an old .xls file format in a a 2007+ version of Excel then you might experience some limitations. It's best to save the file in the modern .xlsx extension unless you have users on Excel 2003 still.
I hope that helps.
With Wildcard it is not working for me. It is just returning number 2. My code is exactly like yours. What could be the reason? Thanks
View Argument Options ALT + down arrow is not working on HP Spectre? How do I see Argument Options? Thanks
Hello Jon - I need your help!! not so much on XLOOKUP, but for "COUNT" function - For example is there a way to "count" multiple visits in a day as only "1" visit?. So if a client visited the doctor's office two times in one day, how do you go about counting it as only '1' visit? - If I have raw data with multiple list of client names, location, service dates and visits, when I do pivot table how can I capture the "count" as 1 visit per day per client, etc (even though they may have multiple appt's in one day)? Thank you in advance!!! I tried everything I know to do, but my calculations adds up all the visits.
It sounds like you want to count the number of unique values. There are ways to do this using formulas in Excel. I don't have any tutorials on it yet, but will add it to the list for the future. A quick search will return a lot of results though.
Once the new dynamic arrays formulas roll out on Office 365, this will become a lot easier with the COUNT(UNIQUE()) formula.
I hope that helps.
@@ExcelCampus thank you!!! I tried "remove duplicates" and redid my pivot table but still no luck..
First, I think that XLOOKUP has taken way too long for MicroSoft to bring the lookup functions up to date. I think it is a real improvement over the "patches" that have been provided in the past.
What happens to duplicate rows. For example if I have to Last names Johnson, VLookup returns the value of the first one always and that is why I use Index Match but ... What does Xlookup do in that case?
If you dont specify the search order... it would be like using vlookup - first value is returned. But you can also reverse the search order to get the last value
XLookup is very helpful as compare to Vlookup
Hii...what if after applying this xlookup .👉👉👉👉 #value! #N/A Came instead of desired enteries... please reply
How could you combine horizontal and vertical look up with xlookup
How i can refresh excel to find it
Hi Khaled, You will need to have an Office 365 subscription and be on the Insiders Fast Channel. Currently the update has only been deployed to a percentage of users on Insiders Fast. So even if you are Insiders Fast, you might still not see it. I believe it will be rolled out to everyone on Insiders Fast soon, but don't know exactly when that will be. Here is a link to the Insiders program. It's free. insider.office.com/
If you are on Insiders Fast then you can try manually updating Office by going to File > Account > Update Options > Update Now. Do this in Excel or any Office application. You should be on Version 1909 (Build 12026.20000 Click-to-Run) Office Insider.
I hope that helps.
Here's a big newbie concept to add to point #12.
you DON'T reference multiple columns in the lookup array, just 1 column there and 1 column in the return array, unlike VLOOKUP. That had me stuck for quite a while :(
Two things about it that disappoint a little. The first is that they didn't add a 6th parameter for value IFNA to save us nesting in IFNA(). The second is that they didn't make it XLOOKUPS() as an obvious follow on from the excellent SUMIFS and COUNTIFS, where more than one lookup criteria can be used.
I have been using XLOOKUP() for dynamically naming parts of ordered lists using the first to last XLOOKUP to get the first instance of a particular value and the last to first search to get the last instance. I can then use a dropdown to dynamically change charts and so on like a slicer would. Imagine the list below starting in cell A1:
APPLE
APPLE
BANANA
BANANA
BANANA
The named range formula: =XLOOKUP(C1,$A$1:$A$10,$A$1:$A$10,0,1):XLOOKUP(C1,$A$1:$A$10,$A$1:$A$10,0,-1)
Which returns A1:A2 if selecting APPLE in C1, or A3:A5 if selecting BANANA.
Hi Rico, Great suggestions! I agree that it would be nice to have some smarter error handling or a value_if_error parameter in the function. In regards to your second suggestion, I believe you could use FILTER for that to return the values in a spill range (dynamic array). FILTER allows you to specify multiple criteria. I briefly show it in this video on Dynamic Arrays (th-cam.com/video/1HF0UGMF070/w-d-xo.html), but I'll do a more in-depth video in the future.
@@ExcelCampus yes, FILTER would do the job, it was really more of a consistency thing I suppose! In terms of naming and usage of formulas I mean. In fact, I'm surprised that filter and xlookup both exist as there is a big overlap in function. It might have been easier for Microsoft just to make one or the other.
can't see the link to download the tool?
Thanks Nissim! I just added the linkt to VLOOKUP Assistant to the description. Here it is: www.excelcampus.com/functions/vlookup-assistant-preview/
? and Wildcard both are not working. I get emails when I don't use ? or Wildcard but with ? and Wildcard, it just returns 2. Why is it not working?
if you use a named range, does that mean you no ;pnger need to use absolute cell references? xxx
Jon - does it work in vba?
Oh great question! It's not there yet, but I'll check with Microsoft to see if it's coming. Thanks! 🙂
I am on Excel 2016. Any chance of this being available on it? I don't have 365 yet!
From the video and from what I read this will only be available in Office 365. So Office 2016 will not get this function.
Walter is correct. At this time I don't believe there are any plans to make it available on older versions of Excel.
How can i get Xlookup in Lenovo computer
Xlookup seems very similar to the filter function
Bruce Quare Very disappointing that this will not be released in Office 2019. Office 365 is still very unstable causing frequent problems with Outlook freezing and also does not like the use of "save as" as this also freezes your computer. Its time a class action was started against MS for releasing software that is not fit for purpose. I am an engineer and it can take us up to two years to check a new version of engineering software before we use it on a live project. We have to know there are no bugs in it.
Thanks Bruce! I agree that the back compat story is going to be the biggest downfall here. MS has a lot of areas to compete with the various platforms (desktop, web, mobile) and customer types to keep happy, and it's proving to be a challenge... 😕
Makes me wonder why XLOOKUP was not in Excel 10-20 years ago.
Its performance heavy. I have definitely noticed that its slower than normal vlookup and index&match
My F4 key does not work as expected
BTY why I don't see the ""Unique" formula after joining the fast insiders? pls advice
I have the same issue. I don't see any of the new dynamic array formulas inspite of having latest version of o365.
You need to sign up the Office Insider program from microsoft website, then you can get the latest version of updates.
@@adng I did with fast insider and still don't see it I see xlookup though
Nissim Lanciano Check and confirm you are using the latest version. Go to File - Account - Update option - update now. And current latest version is 1909(Build 120260.20000)
@@adng I am using the imac ver does it make any difference?