10 Advanced XLOOKUP Tips & Tricks

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:

📺 Watch next:
=============

Advanced Excel Functions - Play List

Top 10 Excel Functions for Data Analysis

Top 10 Excel Functions for Finance People

Top 10 Excel Functions for Data Cleaning

Top 10 Advanced Examples of SUMIFS

How to use XLOOKUP

How to use FILTER

How to use VLOOKUP

How to use IF formula

📚 FULL Excel Course
==================

I run a complete Excel course to make you a rockstar at work. Please check out the program herE:

~

Why can't we add guitars? 🎸+ 🎸
Because they are STRING instruments. 😂

#vlookup #xlookup

[stevegrey]

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.

[jmrineli]

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.

[arjundev]

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.

[PrakashNagaraj]

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

[sarahc]

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!

[barkingkate]

Love your informative videos and your humour, Chandoo! 🙂
A third option for trick 7: =TRANSPOSE(XLOOKUP(TRUE, staff[Salary]=MAX(staff[Salary]), staff))
I initially tried with 1 instead of true, but it returned an NA (probably because there's no operation so the TRUE value is not converted into a number)

[zaidandhman]

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

[shauncamilleri]

@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 a few lines of text attached to the downloadable workbook would be very helpful.

[abhilashn]

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.

[HaileysHomes-ixyu]

The names in the table has me in stitches😂🤣, This is very impactful as always, Thank you Chandoo👍

[arunkhanal]

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!

[ankursharma]

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 😁
& "*", staff[Name], staff[[Name]:[Date of Join]], , 2), 1, 5))

Best Wishes!

[anasahmadindian]

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

[frenan]

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

[gospelmoto]

Another rockin' video. Thanks Chandoo!

[ionut]

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

[FLPanhandle]

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.

[dasthagirimunna]

Anna
Best video when compared to your old videos.


But SQL course 50 queries daggara aagipoindi.
Please explain in Telugu channel also.

[profha]

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

[philipantoni]

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