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!

Very clear and concise. Great tutorial!

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

Briliant!! Very clear instructions !!👏

Exactly what I was looking for. Cheers!

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

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

@OfficeMystic

Жыл бұрын

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

Great tutorial!!! Simple and effective

@OfficeMystic

Жыл бұрын

I'm glad it was helpful!

THIS IS WHAT I NEEDED

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

@OfficeMystic

Жыл бұрын

Glad it helped!

Very helpful! Thanks!

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

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

Exactly what I needed. Thanks!

@OfficeMystic

2 жыл бұрын

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

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

great tutorial. Thank you!!!

So helpful, thank you!!

Amazing tutorial...thanks

THANK YOU! You are a lifesaver!

@OfficeMystic

Жыл бұрын

Glad it helped!

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

THANKS A LOT VERY USEFUL

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

Really helpful.

so good, so easy!

Thank you so much, sir! so so helpful.

@OfficeMystic

2 жыл бұрын

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

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?

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?

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: kzread.info/dash/bejne/Z6Z6ua6gfJPZeM4.html

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

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

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!

Perfect! Please share data file for practice

learnt from you😘

YESSSS

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

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

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?

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

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; 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! Have a use case at work, and this fits the bill!

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?

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

@OfficeMystic

Жыл бұрын

Sure, what's your question?

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

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.

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.

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)