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.
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.
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.
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.
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! 👏
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.
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.
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...
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! 🙂
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.
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?
You’ve missed probably the most major con that this formula does not solve. The majority of the time I use Index/match, I am trying to find the cross section between a dynamic non-known row and a dynamic non known column. E.g =index(array,rowlookup value, column lookup value)...this cannot do that. It’s better than a single argument index/match, but when you need both this doesn’t help.
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
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... 🤷♂️
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...
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. czcams.com/video/1HF0UGMF070/video.html
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
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.
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, 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, 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.
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!
I don't know how your formula =XLOOKUP("*"&B4,$E$4:$E$1003,$G$4:$G$1003,2) worked. 2 is not Match Mode. I think that formula should be =XLOOKUP("*"&B4, $E$4:$E$1003, $G$4:$G$1003, "", 2). Please correct me if I am wrong?
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... 😕
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 :(
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! 🙂
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.
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
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 (czcams.com/video/1HF0UGMF070/video.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.
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.
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])
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.
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!
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 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.
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?
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?
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 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.
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. :-)
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!
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.
1. Yes
2. simplify my life
Excellent video
Thanks Daniel! 🙂
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.
Thank you Jon. Your videos on CZcams and your articles on your website have helped me a lot.
Keep up the good work and God bless You :)
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. 🙂
This is really informative Jon, now i am well aware of the cons, big thanks
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
czcams.com/video/CKVTaJq3B2k/video.html
czcams.com/video/0B9cc7shvoI/video.html
Thanks for this x-loopup video and congratulations with the birth of you daughter Emily!!
Thank you Len! 🙂
Your awesome
No words to express my gratitude
I follow only you for excel tutorial and recommended my frds.
Thanks a lot 🤗
Nicely explained. I appreciate your effort 🙂🙂
v-lookup & h-lookup seem to have made their time... Thanks for the presentation!
your videos are very easy to understand and helpful. Thanks a lot
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! 👏
Look forward to the multiple result return video
Thanks for the vote David! 👍
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! 🙂
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! 🙂
Excellent. Very thorough.
Thanks Oz! 😀
You have a soothing voice 😄
Thank you Chang! 🙂
Hi, Xlookup is great. I will use it for searching for a formula based on what criteria i use. Thanks
great video! Thank you!
You are welcome, AJ! 😀
All your videos are so helpful. Thank you soo much!
Dear please keep updating new functions!!! Thanks, very useful,,,,
Nice lesson, will try it and learn it more
Xlookup is the GOAT
Thank you very much for a great video with comparisons.
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.
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!!!!!
Thank you very much for your great video, Jon! It is very helpful to us.
thank you
Thank you, always a great video from you.
Very good
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! 🙂
Great job Jon, thanks for the detailed explanation. Looks like it will be a nice upgrade when available.
Love your channel. I have learned so much from you, thank you.
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.
Very helpful
Glad it helped! 😀
Great video!
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?
Thanks, Jon for your explanation much appreciated
Thanks Nissim! 🙂
She looks so cute!!!!!!CONGRATULATION!!!!!!!!!!!!!!!!!!!!!!!!
Thank you Ka! 🙂
Funny how they added the if not returned part to this ;)
Thank you, very simplified.
How do I get the vlookup assistance
thanks very new feature. wish my employer had it. had to send a file home, turn around and use my personal laptop...
Thank you so much for the Video. may I ask how to add in Vlookup Assistant?
You’ve missed probably the most major con that this formula does not solve. The majority of the time I use Index/match, I am trying to find the cross section between a dynamic non-known row and a dynamic non known column. E.g =index(array,rowlookup value, column lookup value)...this cannot do that. It’s better than a single argument index/match, but when you need both this doesn’t help.
Try this with google sheets for an alternative
czcams.com/video/CKVTaJq3B2k/video.html
czcams.com/video/0B9cc7shvoI/video.html
Well presented and a fab new feature. Thanks :)
Thanks Denise! 👍
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! 🙂👍
Thanks a lot for the knowledge
Thanks Ochora! 🙂
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... 🤷♂️
I really wish you had mentioned availability before I watched a 21 minute video on something I don't have access to.
Try this with google sheets for an alternative
czcams.com/video/CKVTaJq3B2k/video.html
czcams.com/video/0B9cc7shvoI/video.html
Shut up David
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...
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.
czcams.com/video/1HF0UGMF070/video.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
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
Its great! what about if we have multiple multiple criteria
great explanition has off toyou
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.
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, 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
Wow, i like the way you demonstrate ... Great
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.
if you use a named range, does that mean you no ;pnger need to use absolute cell references? xxx
How could you combine horizontal and vertical look up with xlookup
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!
I don't know how your formula =XLOOKUP("*"&B4,$E$4:$E$1003,$G$4:$G$1003,2) worked. 2 is not Match Mode. I think that formula should be =XLOOKUP("*"&B4, $E$4:$E$1003, $G$4:$G$1003, "", 2). Please correct me if I am wrong?
My F4 key does not work as expected
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... 😕
XLookup is very helpful as compare to Vlookup
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 :(
Q1. yes
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! 🙂
You're amazing 😂
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..
what about if you need to look up multiple iterations of the first reference?
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
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
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 (czcams.com/video/1HF0UGMF070/video.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 you use xlookup with a sum feature that would return the total from a table?
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.
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])
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.
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 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... 😀
Xlookup seems very similar to the filter function
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.
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?
Hii...what if after applying this xlookup .👉👉👉👉 #value! #N/A Came instead of desired enteries... please reply
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, 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.
Ctrl + shift + down
F4
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.
Congratulations on the birth!!!!!!!
Thank you! 🙂
Yikes! Unfortunate that my version of excel doesn't support XLOOKUP :(((
Can a person select the entire columns in the reference?
Yes - tat works too.
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
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. :-)
i really surprised xlookup but i can use in my office how i can use without any
Hi Jon, I downloaded the VLookup Assistant Macro spreadsheet but the “Create Xlookup” Macro was not available.
How can i get Xlookup in Lenovo computer