10 Advanced XLOOKUP Tips & Tricks

Подписаться 615 тыс.

Просмотров 28 тыс.

50% 1

You don't need a guitar to make your data sing 🎶🎵
You just need XLOOKUP.
In this video, I present 10 advanced tricks to master the XLOOKUP function. Dive in and thank me later :)
~
In this video:
0:00 - XLOOKUP Rockstar - 10 advanced examples
0:50 - Setting up data in a table format
1:15 - XLOOKUP Syntax and basic usage (TRICK 0)
2:30 - Get all columns of data with XLOOKUP (Trick 1)
3:35 - Get all columns, but vertically (Trick 2)
4:18 - Lookup values that begins with (wild card match, Trick 3)
5:42 - Equal to or higher than lookup (Trick 4)
7:02 - Multiple conditions with XLOOKUP (Trick 5)
9:44 - Lookup between two dates (Trick 6)
11:52 - Lookup highest value (Trick 7)
14:34 - Advanced Lookup of highest value (Trick 8)
15:38 - Second matching value (Trick 9)
18:13 - Get all matching values with XLOOKUP (Trick 10)
18:44 - Bonus: XLOOKUP but get two different columns.
20:21 - Other important Excel functions for your success
📂 Download the sample file to practice:
=================================
Get the sample file and practice these 10 tricks today:
chandoo.org/wp/wp-content/upl...
Even more examples here: chandoo.org/wp/xlookup-examples/
📺 Watch next:
=============
Advanced Excel Functions - Play List
• Advanced Excel Formula...
Top 10 Excel Functions for Data Analysis
• Top 10 Excel Functions...
Top 10 Excel Functions for Finance People
• If you work in finance...
Top 10 Excel Functions for Data Cleaning
• Data Cleaning in Excel...
Top 10 Advanced Examples of SUMIFS
• 10 *next level* SUMIFS...
How to use XLOOKUP
• I don't use VLOOKUP an...
How to use FILTER
• I don’t use filters in...
How to use VLOOKUP
• Excel VLOOKUP - Compl...
How to use IF formula
• 10 Advanced IF formula...
📚 FULL Excel Course
==================
I run a complete Excel course to make you a rockstar at work. Please check out the program herE:
chandoo.org/wp/excel-school-p...
~
Why can't we add guitars? 🎸+ 🎸
Because they are STRING instruments. 😂
#vlookup #xlookup

Наука

Опубликовано:

19 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 99

@stevegrey9829 7 месяцев назад

Tip 5. Thank you! Until now I have made a helper column concatenating the two values I want to match on and using XLOOKUP. I never thought to use the old SUMPRODUCT syntax.

@soniccheese01 3 месяца назад

What a Rock Star you are!

@gospelmoto2833 7 месяцев назад

Another rockin' video. Thanks Chandoo!

@sarahc172 6 месяцев назад

Thank you! Now I won't have to concatenate multiple columns to create a key to match up my data. Your videos are always SO helpful!

@arjundev4908 5 месяцев назад

Oh my god!! The mocked up data is surely fun to read. Time you invested to get them so humorous definitely needs a praise!! I am liking this salt and pepper look of yours.

@frenan82 7 месяцев назад

That video was GOLD! thanks!! (love the names ;) )

@usmanghori6999 7 месяцев назад

Perfect!

@konata_fan 7 месяцев назад

Excelente video como siempre, muchísimas gracias

@kudifilipe9875 7 месяцев назад

Amazing, thanks...

@chrism9037 7 месяцев назад

X-cellent thanks Chandoo!

@ionut8256 5 месяцев назад

You've just answered SOOOO many of my questions in one go! Fantastic job, and loved the funny names too! ♥

@muyhdynadewale1271 7 месяцев назад

Great, this is amazing

@WaymondJr 7 месяцев назад

This is brilliant 😎👊

@katestan7002 7 месяцев назад

You are awesome !!!

@arbazahmad7177 7 месяцев назад

Excellent..🎉 Cool look 😎

@rashs9901 7 месяцев назад

Rocking 👍

@GourabX 7 месяцев назад

FILTER supremacy. ❤❤

@johnborg5419 7 месяцев назад

Great Video Chandoo!!! Thanks :) :)

@ravikartheek8084 7 месяцев назад

The names in the data are AWESOME.

@lucia26 7 месяцев назад

The clarity. Thank you Chandoo.

@chandoo_ 7 месяцев назад

You're most welcome

@princeofhaspania7200 7 месяцев назад

Great 😃👍

@taizoondean689 7 месяцев назад

Thank You Sir

@anasahmadindian 7 месяцев назад

hats off Bro!!! My Skills now from 🚲to 🚀😆😆

@alexrosen8762 7 месяцев назад

You rock Chandoo 🤙

@shauncamilleri4451 5 месяцев назад

@chandoo, as always thank you for the videos with awesome knowledge. A suggestion if I may, generally we watch the videos on our free time, not when we are nose deep in work, so what I do is I try to keep a summary of the stuff that I learn from the video in an excel, for easier reference further down the line, where I know I saw a solution, but I am not able to do it fully on my own. so in the case of the wildcards where you did xlookup(1,xxxxx) a few lines of text attached to the downloadable workbook would be very helpful.

@carlstiller2261 7 месяцев назад

Excellent Chandoo

@PrakashNagaraj1993 7 месяцев назад

I always use xlookup.. on all my interview i ask candidate to filter uaing xlookup.if they dont know i teach them... every analyst should know this...xlookup.with concat.. and with if conditions..and isna.... it will be fun ... and easy to use..

@jmrineli3177 5 месяцев назад

Example 3 in 1 formula: Option 1: =CHOOSE({1;2},XLOOKUP(D38&"*",staff[Name],staff[Name],,2),XLOOKUP(D38&"*",staff[Name],staff[Date of Join],,2)) Option 2: =TRANSPOSE(XLOOKUP(D38&"*",M5:Q39,{1,5},0)) Very good Content.

@lalit387 3 месяца назад

Thanks for sharing it is very handy for users not having choosecols

@zaidandhman7687 7 месяцев назад

Highest Salary Q7 = TRANSPOSE(FILTER(staff,staff[Salary]=MAX(staff[Salary])))

@pgkannan 7 месяцев назад

You are looking Rock Star ⭐⭐⭐⭐⭐

@chakralamurali 7 месяцев назад

Impressive video sir

@user-jn7tc9zz9t 7 месяцев назад

You're Funny today first Intro 🤣🤣🤣🤣🤣🤣

@MehediHasan-hk9mk 7 месяцев назад

What an into Chandoo Bhai. ❤

@balamira297 7 месяцев назад

Thank you Chandoo!

@chandoo_ 7 месяцев назад

Any time

@arunkhanal7208 2 месяца назад

Hey Mr. Candoo (Purna D.), loved your video on XLOOKUP! Quick tip: In math, comparisons with weak inequality ("higher than") often come before "or equal to." So in example 4, "Salary Higher than or equal to..." might be clearer. Thanks for the awesome content!

@chandoo_ 2 месяца назад

Thanks for the tip. I will keep it in mind next time I use that.

@stevenp3007 5 месяцев назад

Very nice video, well done

@FLPanhandle 6 месяцев назад

Excellent video as always, and I just upgraded from Excel 2010 to Excel 2021 which now gives me XLookup. It does not provide the Take function though, so I'm puzzled at how to get just the first row in your Transpose(Take(Sort))) example. I saw where the Offset function can be used in lieu of Take, but though I've used Offset for years creating Dynamic Formulas the old way, I have not figured out the correct formula yet.

@abhilashn2993 3 месяца назад

Nice Video. Even names of film actors are creative( Rai as Row,Bachan as function ,khan as scan ,kanth as coth,Bill as Fill)... Trick 9 was innovative... CHOOSECOLS was not working in my excel so used index(xlookup(condition),1,{1,5}) then it worked.

@TechGuru-ij6zi 7 месяцев назад

@Chandoo how to apply x lookup or vlookup while looking for data between multiple sheets?

@Gareeb_ 3 месяца назад

Th😊nks

@meditationomshanti1976 7 месяцев назад

Hi Chandoo... looking TAPORI😂 but great help as usual...

@profha2 7 месяцев назад

Great videos 📹 👌 👏 👍. How long do these videos take to put it together?

@doug4853 7 месяцев назад

Thank you Chandoo. I needed a good laugh and some great analysis tips today.

@Satyendra2022 7 месяцев назад

Hello 👋👋👋👋👋👋👋 Sir Which Version are you using please tell me Sir......

@chandoo_ 7 месяцев назад

I am using Excel 365 in this video.

@andriim3661 7 месяцев назад

Nice look style and cool beard

@bosnianinny 7 месяцев назад

Excellent content, as always :)

@dasthagirimunna7017 7 месяцев назад

Anna Best video when compared to your old videos. But SQL course 50 queries daggara aagipoindi. Please explain in Telugu channel also.

@dakshbhatnagar 7 месяцев назад

14:28 I can use filter with max function and then use index to find the guy with max salary assuming there are multiple guys with the same salary or this could be the case with the joining date example as well.

@chandoo_ 7 месяцев назад

You can. In case of multiple people with max salaries (or anything similar), and you just want to see one of them, you use XLOOKUP as it will be faster than FILTER.

@learner6601 7 месяцев назад

Hi sir, a suggestion of one video to you sir, meru Trading Journal Dashboard in Excel, create cheyandi sir, it will be helpful to Traders and Investors along with DA's. Regards, Vin

@philipantoni679 7 месяцев назад

I love your Humor!!! Awesome entertaining premium content 😊 Thank you, Chandoo!

@chandoo_ 7 месяцев назад

You are welcome Philip...

@shoaibrehman9988 7 месяцев назад

Thanks, Chandoo. Awesome work.

@pradipmajhi8636 7 месяцев назад

Dear sir please bring video tutorial on data cleaning on Excel to get deep knowledge of it.

@Excelambda 7 месяцев назад

Great video!! Binary search can save lives when comes to speed , for sorted sets. Anyhow, the best XLOOKUP trick is the FILTER 😂✌

@chandoo_ 7 месяцев назад

😂

@mokshitrambhia3714 7 месяцев назад

Amazing thumbnail 😂

@chandoo_ 7 месяцев назад

Glad you think so. I had so much fun shooting this video and making the thumbnail + edits.

@adityamohan7366 7 месяцев назад

11:43 you didn't give a solution on how to make it return multiple values. Although other parts of the video are fantastic. But Im curious if xlookup can return multiple rows or we need to go the usual Index Large route.

@Excelambda 7 месяцев назад

=FILTER(staff[Name],(staff[Date of Join]>=--"20-Mar-22")*(staff[Date of Join]

@adityamohan7366 7 месяцев назад

@@Excelambda this works. But do you know if we can still do it with Xlookup??

@Excelambda 7 месяцев назад

@@adityamohan7366 FILTER in the native "truth" (TRUE) seeker. Can be done but still will need filter, so it's like turning the building if you want to screw in a light bulb.😉

@chandoo_ 7 месяцев назад

That was Trick #10 - the FILTER option.

@jeffinjordan 7 месяцев назад

Thanks, Chandoo. It still amazes me that I can be on the complete opposite side of the world and get notified within 15 seconds!

@Azhar_Khan383 7 месяцев назад

This was a fantastic learning experience how to use formulas and It will add value to my life

@invincible9971 4 месяца назад

The tricks are very helpful. I tried practising with the dataset provided, but in each and every tricks, I have got #NAME? error. I don't know why. Please help.

@chandoo_ 4 месяца назад

May be you misspelled something or you don't have xlookup.

@taizoondean689 7 месяцев назад

Can we use Max formula for getting person with highest salary with Xlook up

@chandoo_ 7 месяцев назад

You can :)

@chinallukhi 7 месяцев назад

the way he wrote actors, politician and cricketer's name 🤣

@faridqanet4709 4 месяца назад

Example 5: I have done it with the following formula: =XLOOKUP(D68&D67, staff[Job Title]&staff[Gender], staff[Name],,0) I don't know which one is efficient?

@contactauditssss6122 7 месяцев назад

Sir Which version of excel are you using Please comment

@chandoo_ 7 месяцев назад

I use Excel 365 in most of my videos. XLOOKUP is only available in 365 or Excel on Web.

@dmentertainment4186 7 месяцев назад

One like for your entry 🎉

@taizoondean689 7 месяцев назад

Happy Diwali

@MohamedIsmail-hw5il 7 месяцев назад

hilarious names 🤣😂

@Hello-bn2yc 7 месяцев назад

I can tell you are working on your guitar tracks..

@BISimplifier 7 месяцев назад

Good,One friendly advice,don't overuse visual effects. !!

@chandoo_ 7 месяцев назад

Thank you. What part of the visual effects in the video felt overused to you?

@equiwave80 6 месяцев назад

Hi Chandoo, This was an excellent video. Given below are my answers (BTW I did not see your solution in the end of the video) Challenge: One formula to get both results =TRANSPOSE(XLOOKUP(D38&"*",staff[Name],CHOOSE({1,2}, staff[Name],staff[Date of Join]),,2)) Alternate Solution for Example 6 to get the two people joined in those dates: =FILTER(staff[Name],(staff[Date of Join]>=D85)*(staff[Date of Join]

@commanman974 7 месяцев назад

Le me who uses office 2007 😀🙃

@jerrydellasala7643 7 месяцев назад

Example 5 - the formula "=XLOOKUP(D67&D68,staff[Gender]&staff[Job Title],staff[Name])" is shorter & clearer IMHO Alternate Example 7: " =TRANSPOSE(FILTER(staff,staff[Salary]=MAX(staff[Salary])))"

@cinemax4D 3 месяца назад

How are you keeping a straight face and not cracking up reading the names, like what the heck is Shahrukh scan, choosevelt?!!!😭😭😭😭😭

@Swanandk-nc8rs 3 месяца назад

Example no 5 does not work in excel

@DomingosCJM 3 месяца назад

I missed XLOOKUP inside XLOOKUP.

@ExcelStrategy 7 месяцев назад

🤣🤣🤣🤣🤣🤣

@ankursharma6157 7 месяцев назад

Hi Chandoo, ✓ For Example 5 (2 conditions), I used: =XLOOKUP(D67 & D68, staff[Gender] & staff[Job Title], staff[Name]) ✓ For Example 7, inspired by Your previous tricks, I used the following: =TRANSPOSE(XLOOKUP(TRUE, staff[Salary]=MAX(staff[Salary]),staff)) ✓ For Example 9, I used: =CHOOSEROWS(FILTER(staff[Name], staff[Job Title] = D126), 2) ✓ My formula for the challenge (Example 3) was SAME as Your's Truly 😁 =TRANSPOSE(CHOOSECOLS(XLOOKUP(D38 & "*", staff[Name], staff[[Name]:[Date of Join]], , 2), 1, 5)) Best Wishes!

@COURSSTATSCHAMBERY 7 месяцев назад

Q3 : =xlookup("John*";Steff[Name];Hstack(Staff[Name];Staff[Date]);;2) 🙂 Btw thanks for all your impressive videos and the editing work is very very appreciate Q7: The sort method is not completly equivalent with the Xlookup. If you have a text in a cell (like "Information missing") the sort fonction gives you tne name of the first instance of the personn with "information missing". Two methods possible with Xlookup :=transpose( xlookup(1E+300;Staff[Salary];Staff;;-1)) or =transpose(1;rank(Staff[salary];Staff[Salary];0);Staff). But Filter will be much better 🙂

@chandoo_ 7 месяцев назад

Great use of HSTACK... Of course, if your data has missing or incorrect values, sorting alone can't help.

@kudifilipe9875 7 месяцев назад

5. =XLOOKUP($D$67&$D$68,staff[Gender]&staff[Job Title],staff[Name])