XLOOKUP with Multiple Conditions
Vložit
- čas přidán 5. 04. 2022
- XLOOKUP is one of the most useful formulas in Excel, and in this video, we explore how to use it with multiple conditions. You would use an XLOOKUP with multiple conditions if you want to satisfy more than one condition, and in this case, there are two, the month and the year. If the formula finds a match for both the month and the year, it will pull the corresponding data.
The XLOOKUP function is only available in Excel 365.
I also have a video exploring a single condition XLOOKUP scenario.
#xlookup #excel #formulas #office365 #excel365 #multiple #conditions
~-~~-~~~-~~-~
Please watch: "Excel Top 5 Flash Fill Excel"
• Excel Top 5 Flash Fill...
~-~~-~~~-~~-~
![XLOOKUP with Multi Criteria Lookup | How to use XLOOKUP multi criteria? [Never seen this before!!!]](http://i.ytimg.com/vi/e7ofJ6Rz1nk/mqdefault.jpg)
That & concatenate in xlookup is a game changer. Exactly what I needed. Thank you soo much! Subscribing.
The cleanest solution that I've seen! Thanks a lot for this!
I needed to know how to do an XLOOKUP across two sheets and two conditions and watched countless videos that took forever to give the most complicated way to do XLOOKUP. I've now learned there are multiple ways to write these formulas that still follow the same general rules. The difference is you can go super complicated OR you can do it this way by Office Mystic. By far the most straight-forward explanation. A short video that gets straight to the point and easy to understand. WOW!! Thank you and Subscribed.
I've looked at many videos and yours was the best by far!
easy to follow, short and to the point. excellent, thank you very much.
Very clear and concise. Great tutorial!
Briliant!! Very clear instructions !!👏
Exactly what I was looking for. Cheers!
Very helpful! Thanks!
Great tutorial!!! Simple and effective
I'm glad it was helpful!
Honestly best example and also short and sweet. Thank you very much.
Thanks for commenting! I'm glad you found the video helpful.
great tutorial. Thank you!!!
So helpful, thank you!!
this really help me thank you
THIS IS WHAT I NEEDED
Exactly what I needed. Thanks!
I'm glad it was helpful and that you were able to find my video! Have a great weekend.
Amazing tutorial...thanks
God bless you, being looking for this video for weeks now, hope it works on Table.
THANKS A LOT VERY USEFUL
Fantastic, the formula worked first time. Great, should be saving me a tonne of time at work now.
Glad it helped!
First time seeing one of your videos..... Well deserved LIKE, and sub!
You are the best i was looking for this 2 days now
Really helpful.
Cool stuff! Thx for this, helped me a lot 😀
THANK YOU! You are a lifesaver!
Glad it helped!
so good, so easy!
Great video, wanted to know the difference of this approach to Boolean ? I deal with large data sets snd andand and more guidance on the approach. Thanks
Thank you so much, sir! so so helpful.
Thanks for dropping by and leaving a comment. I'm glad the video was helpful!
Such a clear video and something I really needed. At my job we use Google Sheets, and excel is very slow on my computer. This formula returns an error in sheets "Array arguments to XLOOKUP are of different size." Any idea how to fix this?
Wow life saver! I wonder what the performance is compared to something like index match
Can you do this for many columns? What if you want a "fuzzy" lookup where there may be a slight change in spelling in one of your columns?
Have aquestion. For lookup range can we select 2 or 3 columns together without using &....????
How do I have it return multiple records that meet that condition and add them up. It returns only the first record that meets the criteria, but there are more that need to be added into one. Thank you!
Thanks!!!
No problem!
It's a great video. I want to x lookup data in to one column but only if that column is empty then i want to take data from other column. so basically i want to x look up data from two columns in one cell. how can i do. when i use two xlookup it gets data from first column and doesn't take data from other column. Would you be able to help ? Thank you
Perfect! Please share data file for practice
learnt from you😘
YESSSS
This video help me a lot and saved my time👍thanks
Glad to hear it!
Good video. Also really liked you you kept it short. I kept trying to nest and couldn't get it to work. I would be old and gray by the time I figured your solution out. Wait. I am already old and gray, but you know what I mean.
thankyou
Thanks a lot for the video. It is very useful. But i still have a question. If i have returt array under conditions how i make it? In your example you have only one correct number "record". If i have 4 different records a,b,c,d where
1. If a>0, b>=0, c>=0, d>=0 choose a,
2. If a=0, b>0, c>=0, d>=0 choose b,
3. If a=0, b=0, c>0, d>=0 choose c,
4. If a=0, b=0, c=0, d>0 choose d
Thanks in advance
if january and 2011 there, how to show in only one column ? thankyou
What if I have three criteria, where two are in columns and one is in a row?
trying to do it on Google sheets and getting error message "array argument to xlook up are of a different size".
I have problems with this formula, what happens if I have 2 instances of the result and I want to see them as you would do, for example if you have 2 records in June 2010, how can I visualize them, because I know that it will find it, but it will only return the value of the first one I really appreciate the hep
good example, what if I have more than 1 record that match the multiple conditions, in you example, I have >1 record with January 2011, say 1265, 1111,2111 and so on
hello I am trying to use the xlookup. i have 2 criteria but based on this criteria i need to pull data from 2 different sources. example: i have a choice between service and construnction (first criteria) and 2 different lists of locations (i have both of these in dropdowns and working.) the result of these 2 criteria need to return the labor dollar based on whether service or construction. please help
Hello Steve. I'll need to take a look at the spreadsheet. If you're comfortable with sending me a modified version that contains only the first 5 rows, I can take a look at it; OfficeMystic@protonmail.com. I think it just needs an IF statement with the XLOOKUP nested in. I'm working on another project tonight, but I can take a look at it tomorrow.
Subscribe for more! tinyurl.com/mu8f835c
Welcome to Office Mystic! If you have any questions on this function or have recommendations, please comment below. Here is a link to a single condition XLOOKUP: czcams.com/video/5rFWLgL0sHk/video.html
Having an issue when using data in tables. Does this method not work in tables?
It should work in tables as well. What kind of problem are you having?
Thanks! Have a use case at work, and this fits the bill!
You don't need absolute values?
No, it's not necessary. If you can provide an example where it's not working, I can take a look at it.
thank you so much sir
but i have one question can you help me please ?
Sure, what's your question?
Not working in my google sheet
I'm sure there are different functions in Google sheets. Unfortunately, I'm not familiar with it.
How can we do xlookup if we have several variables
Like if cell A1 = 1234 AND B1 = PPPP, PUT value as correct ESLE Other condition like mention above
Sorry for the long delay, I'm sure you've figured it out by now. I've been away but will be returning soon. You can string as many conditions you want with a "&"
Couldn't you have just looked up using a single data since they're in different columns? Like just using the year, or just the month? I don't really understand the use case here
Good afternoon. I just wanted to show the process in action and thought it would be easier by keeping everything on the same tab to cut down on the time.
@@OfficeMystic Got it, thanks for responding
It's so easy to demonstrate on a worksheet with so little data. Let's see you try to do this on a worksheet with something like 20,000 rows. Excel chokes, hangs and I have to force-close it (I have 16GB of RAM). So, does not really work in my case.
This is simply to show how a formula works.
16gb? What is this 2015? I've had over 100k rows and excel doesn't choke.
@@BeaverMonkey Well, I've entered the formula on my Excel data (with 3 conditions). Now, after about an hour of calculating, Excel shows at the bottom 1%. The computer is barely responsive, CPU at 100%. This is going to be an all nighter...hope the computer doesn't crash mid way.
This is NOT multiple conditioned IF statement -- this is using concatenation to combine lookup values. Misleading
Do you see any issues with this formulae as it doesn't return a value. All references are correct =XLOOKUP(A7&B7,'Asia Pacific Master'!B:B&'Asia Pacific Master'!C:C,'Asia Pacific Master'!E:E,"-",0,1)