XLOOKUP with Multiple Conditions
Вставка
- Опубліковано 2 жов 2024
- 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...
~-~~-~~~-~~-~
The cleanest solution that I've seen! Thanks a lot for this!
I've looked at many videos and yours was the best by far!
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: ua-cam.com/video/5rFWLgL0sHk/v-deo.html
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
Very clear and concise. Great tutorial!
Great tutorial!!! Simple and effective
I'm glad it was helpful!
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
This is NOT multiple conditioned IF statement -- this is using concatenation to combine lookup values. Misleading
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
How do you use the lookup value is a range instead of a single value?
THANK YOU!! You saved me nearly two hours of work each week 🙌🏼🙌🏼🙌🏼🙌🏼
I'm glad it was helpful!
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!
if january and 2011 there, how to show in only one column ? thankyou
trying to do it on Google sheets and getting error message "array argument to xlook up are of a different size".
Could you please explain the last parameter in the formula
hh just waw, so neat, so clean, thank you so much
Honestly best example and also short and sweet. Thank you very much.
Thanks for commenting! I'm glad you found the video helpful.
Thanks! Have a use case at work, and this fits the bill!
easy to follow, short and to the point. excellent, thank you very much.
Briliant!! Very clear instructions !!👏
this saves me so much time. Thank you!
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?
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)
Love it!!! Thank you
this really help me thank you
Thanks à lot, really appreciated. I have been struggling to get my way out until I got this video. 😊
That & concatenate in xlookup is a game changer. Exactly what I needed. Thank you soo much! Subscribing.
I spent an inordinate amount of time today researching what everyone else made soooo complicated. Thanks 😊!
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.
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
Have aquestion. For lookup range can we select 2 or 3 columns together without using &....????
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
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.
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?
Awesome! I badly needed in one of my work needs, thank you very much for sharing!
Wow life saver! I wonder what the performance is compared to something like index match
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
First time seeing one of your videos..... Well deserved LIKE, and sub!
Thanks a lot.
God bless you, being looking for this video for weeks now, hope it works on Table.
Excellent!
Thanks.
What if I have three criteria, where two are in columns and one is in a row?
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 "&"
Cool stuff! Thx for this, helped me a lot 😀
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.
You are the best i was looking for this 2 days now
Not working in my google sheet
I'm sure there are different functions in Google sheets. Unfortunately, I'm not familiar with it.
So helpful, thank you!!
great tutorial. Thank you!!!
Exactly what I was looking for. Cheers!
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?
Fantastic, the formula worked first time. Great, should be saving me a tonne of time at work now.
Glad it helped!
thank you so much sir
but i have one question can you help me please ?
Sure, what's your question?
THANKS A LOT VERY USEFUL
Amazing tutorial...thanks
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.
Exactly what I needed. Thanks!
I'm glad it was helpful and that you were able to find my video! Have a great weekend.
Very helpful! Thanks!
Perfect! Please share data file for practice
learnt from you😘
Thanks!!!
No problem!
so good, so easy!
This video help me a lot and saved my time👍thanks
Glad to hear it!
thankyou
Really helpful.
YESSSS
THIS IS WHAT I NEEDED
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! You are a lifesaver!
Glad it helped!
Thank you so much, sir! so so helpful.
Thanks for dropping by and leaving a comment. I'm glad the video was helpful!