XLOOKUP with Multiple Conditions

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...
~-~~-~~~-~~-~

[@wildchildOz]

The cleanest solution that I've seen! Thanks a lot for this!

[@LIV-FREE-VET]

That & concatenate in xlookup is a game changer. Exactly what I needed. Thank you soo much! Subscribing.

[@mukundsetlur3141]

Awesome! I badly needed in one of my work needs, thank you very much for sharing!

[@zeromeansnada]

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.

[@percyveer2355]

easy to follow, short and to the point. excellent, thank you very much.

[@GreatDataVideos]

I've looked at many videos and yours was the best by far!

[@wminnebo]

Very clear and concise. Great tutorial!

[@nm90halisi]

Exactly what I was looking for. Cheers!

[@Uvaz78]

Briliant!! Very clear instructions !!👏

[@eehutchings]

Honestly best example and also short and sweet. Thank you very much.

[@OfficeMystic]

Thanks for commenting! I'm glad you found the video helpful.

[@luigig867]

Great tutorial!!! Simple and effective

[@OfficeMystic]

I'm glad it was helpful!

[@TheFroman09]

Exactly what I needed. Thanks!

[@OfficeMystic]

I'm glad it was helpful and that you were able to find my video! Have a great weekend.

[@aquayum72]

Fantastic, the formula worked first time. Great, should be saving me a tonne of time at work now.

[@OfficeMystic]

Glad it helped!

[@MelBaena]

So helpful, thank you!!

[@felicetapaganao3873]

great tutorial. Thank you!!!

[@j_barrett]

Very helpful! Thanks!

[@azharmubarok7790]

Amazing tutorial...thanks

[@turoochoa2546]

First time seeing one of your videos..... Well deserved LIKE, and sub!

[@RustonRemote]

THIS IS WHAT I NEEDED

[@tnex]

God bless you, being looking for this video for weeks now, hope it works on Table.

[@destineem8975]

THANK YOU! You are a lifesaver!

[@OfficeMystic]

Glad it helped!

[@black_south5222]

You are the best i was looking for this 2 days now

[@sandeepkumar270]

THANKS A LOT VERY USEFUL

[@TheKotakot]

this really help me thank you

[@waqassm1]

Really helpful.

[@bella1210]

Thank you so much, sir! so so helpful.

[@OfficeMystic]

Thanks for dropping by and leaving a comment. I'm glad the video was helpful!

[@Joda-es5xd]

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

[@elizabethacerra2598]

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?

[@danielrosochacki1761]

Cool stuff! Thx for this, helped me a lot 😀

[@avramidisjeremy]

so good, so easy!

[@peculiarfilm]

Wow life saver! I wonder what the performance is compared to something like index match

[@usmaniqbal1836]

Perfect! Please share data file for practice

[@lianchingyap5023]

Thanks!!!

[@OfficeMystic]

No problem!

[@yoongiwifeinthenextlife]

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?

[@sidghosalkar8291]

This video help me a lot and saved my time👍thanks

[@OfficeMystic]

Glad to hear it!

[@tanweerabbasabbas649]

learnt from you😘

[@user-ii2gk1ye2u]

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

[@juancarlospaz4071]

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!

[@tanweerabbasabbas649]

Have aquestion. For lookup range can we select 2 or 3 columns together without using &....????

[@lanceross1035]

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.

[@AnferAlan]

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

[@vai_ieky]

thankyou

[@OfficeMystic]

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: kzbin.info/www/bejne/a6OpiH-dgZWmfs0

[@user-zp7xs4nh2c]

YESSSS

[@Takeair_Israel]

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

[@cradlemoutain]

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

[@Indrastemcart]

if january and 2011 there, how to show in only one column ? thankyou

[@orangecountyhouse]

trying to do it on Google sheets and getting error message "array argument to xlook up are of a different size".

[@KB-nk7nk]

What if I have three criteria, where two are in columns and one is in a row?

[@stevegayle6923]

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

[@OfficeMystic]

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; [e-mail]. 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.

[@shoaibmohammad9847]

thank you so much sir but i have one question can you help me please ?

[@OfficeMystic]

Sure, what's your question?

[@jeffsteinhauer3885]

Having an issue when using data in tables. Does this method not work in tables?

[@OfficeMystic]

It should work in tables as well. What kind of problem are you having?

[@fliptop157]

Thanks! Have a use case at work, and this fits the bill!

[@jofartknocker]

You don't need absolute values?

[@OfficeMystic]

No, it's not necessary. If you can provide an example where it's not working, I can take a look at it.

[@akashpatil2676]

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

[@OfficeMystic]

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 "&"

[@IsrafilHossein-t6i]

Not working in my google sheet

[@OfficeMystic]

I'm sure there are different functions in Google sheets. Unfortunately, I'm not familiar with it.

[@user-ge5fq2zg4p]

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

[@OfficeMystic]

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.

[@user-ge5fq2zg4p]

@@OfficeMystic Got it, thanks for responding

[@1HDBIZ]

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.

[@OfficeMystic]

This is simply to show how a formula works.

[@BeaverMonkey]

16gb? What is this 2015? I've had over 100k rows and excel doesn't choke.

[@1HDBIZ]

@@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.

[@markk364]

This is NOT multiple conditioned IF statement -- this is using concatenation to combine lookup values. Misleading

[@brianross3983]

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)