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.
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?
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
HI.. I am trying to do a X lookup to match conditions for 2 cells. The first condition is a column and the 2nd condition is a row. Match the 2 conditions and than go down that columns to return that value that match the row. Can you help.
I'm trying to use two drop down menus that would determine which colomn. I have a list of states with tax rates for sigle or married filers. I have another sheet that has the state drop down and tax filing status, but it needs to reference the single rates when that drop down is selected and the MFJ aray column when that is selected in the drop down. to complicate things a bit more. some states have multiple tax rates depending on income ranges. so I'm not quite sure how to use the xlook up with "if" formulas to accomplish my goal.
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!
My second criteria/condition is to check if B2 is equal or greater than today. without what I'm calling a 2nd condition, my formula looks like this =XLOOKUP(DataTable[NAME],LoansTable[Name],LoansTable[Installment],"") but i want it to return blank if the date in the data table from G5 is less than today. Would be able to guide?
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
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.
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
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
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: ruclips.net/video/5rFWLgL0sHk/видео.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
Hi, thank you for the video! Can you help me to combine multiple conditions on a single array? For example, IF( XLOOKUP(lookValue, lookupArray, returnArray) = {"Condition1" OR "Condition2"}, "ThenValue", "ElseValue") My multiple condition is all within the same column for my IF statement, if this makes sense...Arrrgh!
Found it!! This can be done by combining the IF statement with an OR statement: IF(OR(Xlookup_condition1,Xlookup_condition2), ThenValue, ElseValue)...maybe, we'll see :)
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.
@ Don’t be too hard on yourself. The content is solid and the delivery is flawless. Kudos and keep serving us with these career boosting videos. Cheers
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.
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.
@@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.
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)
![XLOOKUP with Multi Criteria Lookup | How to use XLOOKUP multi criteria? [Never seen this before!!!]](http://i.ytimg.com/vi/e7ofJ6Rz1nk/mqdefault.jpg)
The cleanest solution that I've seen! Thanks a lot for this!
thank you so much! I was rushing the end of a project and needed this refresher! 10/10
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!
Briliant!! Very clear instructions !!👏
I spent an inordinate amount of time today researching what everyone else made soooo complicated. Thanks 😊!
Agreed on all the other comments - simple solution! No other videos were what I was looking for. Thanks!
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?
Very clear and concise. Great tutorial!
THANK YOU!! You saved me nearly two hours of work each week 🙌🏼🙌🏼🙌🏼🙌🏼
I'm glad it was helpful!
Great tutorial!!! Simple and effective
I'm glad it was helpful!
easy to follow, short and to the point. excellent, thank you very much.
Honestly best example and also short and sweet. Thank you very much.
Thanks for commenting! I'm glad you found the video helpful.
Really helpful ❤
Very nicely done! Thank you. In this specific example though, wouldn't you think Slicers would be a faster intuitive way of solving the problem?
Yes. That would definitely be a viable option. I will cover it in a future video once I have time to focus on the channel again.
That & concatenate in xlookup is a game changer. Exactly what I needed. Thank you soo much! Subscribing.
great explanation and easy solution. Thank you!
Simple but good solution!!!👍🏻
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
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!
Thanks à lot, really appreciated. I have been struggling to get my way out until I got this video. 😊
Awesome! I badly needed in one of my work needs, thank you very much for sharing!
HI.. I am trying to do a X lookup to match conditions for 2 cells. The first condition is a column and the 2nd condition is a row. Match the 2 conditions and than go down that columns to return that value that match the row. Can you help.
this saves me so much time. Thank you!
God bless you, being looking for this video for weeks now, hope it works on Table.
I'm trying to use two drop down menus that would determine which colomn. I have a list of states with tax rates for sigle or married filers. I have another sheet that has the state drop down and tax filing status, but it needs to reference the single rates when that drop down is selected and the MFJ aray column when that is selected in the drop down. to complicate things a bit more. some states have multiple tax rates depending on income ranges. so I'm not quite sure how to use the xlook up with "if" formulas to accomplish my goal.
Have aquestion. For lookup range can we select 2 or 3 columns together without using &....????
You are the best i was looking for this 2 days now
Could you please explain the last parameter in the formula
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!
Wow life saver! I wonder what the performance is compared to something like index match
Exactly what I was looking for. Cheers!
How do you use the lookup value is a range instead of a single value?
My second criteria/condition is to check if B2 is equal or greater than today.
without what I'm calling a 2nd condition, my formula looks like this =XLOOKUP(DataTable[NAME],LoansTable[Name],LoansTable[Installment],"") but i want it to return blank if the date in the data table from G5 is less than today.
Would be able to guide?
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
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.
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
Amazing tutorial...thanks
if january and 2011 there, how to show in only one column ? thankyou
Exactly what I needed. Thanks!
I'm glad it was helpful and that you were able to find my video! Have a great weekend.
Love it!!! Thank you
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
Excellent!
Thank you!
THANK YOU! You are a lifesaver!
Glad it helped!
THIS IS WHAT I NEEDED
great tutorial. Thank you!!!
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?
So helpful, thank you!!
Very helpful! Thanks!
THANKS A LOT VERY USEFUL
hh just waw, so neat, so clean, thank you so much
Really helpful.
trying to do it on Google sheets and getting error message "array argument to xlook up are of a different size".
Cool stuff! Thx for this, helped me a lot 😀
Perfect! Please share data file for practice
This video help me a lot and saved my time👍thanks
Glad to hear it!
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: ruclips.net/video/5rFWLgL0sHk/видео.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
What if I have three criteria, where two are in columns and one is in a row?
so good, so easy!
this really help me thank you
Thanks.
Hi, thank you for the video! Can you help me to combine multiple conditions on a single array? For example, IF( XLOOKUP(lookValue, lookupArray, returnArray) = {"Condition1" OR "Condition2"}, "ThenValue", "ElseValue")
My multiple condition is all within the same column for my IF statement, if this makes sense...Arrrgh!
Found it!! This can be done by combining the IF statement with an OR statement: IF(OR(Xlookup_condition1,Xlookup_condition2), ThenValue, ElseValue)...maybe, we'll see :)
thank you so much sir
but i have one question can you help me please ?
Sure, what's your question?
Thanks a lot.
Thank you so much, sir! so so helpful.
Thanks for dropping by and leaving a comment. I'm glad the video was helpful!
learnt from you😘
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?
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.
Thanks!!!
No problem!
But this Not multiple conditions. This is multiple criteria. This video title is misleading though the content is solid.
You're right. I don't know what I was thinking.
@ Don’t be too hard on yourself. The content is solid and the delivery is flawless. Kudos and keep serving us with these career boosting videos. Cheers
Thanks! Have a use case at work, and this fits the bill!
thankyou
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.
YESSSS
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 "&"
Not working in my google sheet
I'm sure there are different functions in Google sheets. Unfortunately, I'm not familiar with it.
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.
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)
This is NOT multiple conditioned IF statement -- this is using concatenation to combine lookup values. Misleading