What the function - XLOOKUP for Excel in simple words

Welcome to what the function - a new series of no nonsense guides on Excel functions.
In the first instalment, let's look at XLOOKUP, the VLOOKUP killer.
Use XLOOKUP to find matching data from your tables or ranges. The syntax for XLOOKUP is,
=XLOOKUP(what you want to lookup, lookup range, result range, optional if error value)
XLOOKUP will return EXACT match by default.
For more information on XLOOKUP, please see this page -
chandoo.org/wp/xlookup-examples/
#xlookup #WhatIsXlookup #VlookupAlternative

[@duurduranto]

I love how you explain things. It's so much easier to understand than many other people teaching the same thing

[@sameer9045]

I really addicted to your Excel videos. But if you add the data set as well with the video, that would be even more better.

[@chandoo_]

Thank you. I have datasets / sample files for almost all the videos. Please check the video description. For this one, you can also visit chandoo.org/wp/xlookup-examples/

[@sadashivpal9423]

I have been a deep follower on mailing since long time. Just love your tactics and trainings.

[@MrBillava]

Thanks, Chandoo....it was very insightfully especially the transpose & Xlookup combination

[@MRaedAlLulu]

Thank you, Chandoo.. You are the genies !

[@funwithfatty6067]

I want 2 pray more subscribers will joint this channel and get the valuable knowledge from your videos, just like me... Whenever I stuck in excel dashboard creation I have visited to your website and followed your instructions to resolved my issue. Thank you

[@asimsiddiq475]

sir, your every ms excel tutorial very very informative for me, thank a lot.

[@qaisurrehman]

Awesome , just like all your videos!

[@wayneedmondson1065]

Hi Chandoo.. great stuff. XLOOKUP is so powerful and versatile. Thanks for the examples. Also, thanks for the sample workbook from your blog. I worked through all the examples and learned a lot. I especially like your formula for returning the Net Sales of the second person with the name Jamie.. is in: =XLOOKUP(H16&"2",FILTER(sales[Sales Person],sales[Sales Person]=H16)&SEQUENCE(3),FILTER(sales[Net Sales],sales[Sales Person]=H16)). Very clever use of concatenation on the lookup_value argument of XLOOKUP and the include argument of FILTER to coax out the item from the second record.. excellent! Thanks for the great insights there. Thumbs up!!

[@chandoo_]

Thanks Wayne... Since writing that post, I learned a shorter version of the same formula (that avoids XLOOKUP altogether). Here it is =FILTER(FILTER(sales[Net Sales], sales[Sales Person]="Jamie"), SEQUENCE(COUNTIFS(sales[Sales Person], "Jamie"))=2)

[@wayneedmondson1065]

@@chandoo_ Excellent.. thanks for sharing the alternate formula! Inspired by your construct, here is another one I just created which is even a bit shorter/simpler: =INDEX(FILTER(sales[Net Sales],sales[Sales Person]="Jamie"),2). Some great learning on this exercise. Thumbs up!!

[@chandoo_]

Wow... That is easier. I am going to use it from now :)

[@Teenageo1]

Great tips, always! Thanks

[@chandoo_]

Glad you like them!

[@hazemali382]

more than great ♥

[@ankitbhardwaj4595]

It's so amazing formula Thanks for it

[@chandoo_]

Thank you Ankit...

[@DROIDFARM]

New subscriber here! Thank you!

[@chandoo_]

Thanks for subbing!

[@atifr]

Wonderful video. Just I was searching how to compare data from 3 columns of 2 tables and get the result?

[@MrAeolson]

thanks great tutorial. Goodbye vlookup

[@sandipgumtya130]

Hi Chandoo.thanks . Can you please cover the INDIRECT and OFFSET function and it's usage with ROW or Column function?

[@chandoo_]

Thanks for these suggestions Sandip. I will cover them in future episodes. If you want, check out these pages for a good explanation on OFFSET and making running numbers: chandoo.org/wp/offset-formula-explained/ chandoo.org/wp/rows-and-columns-excel-formulas/

[@sandipgumtya130]

@@chandoo_ thanks a lot. I shall be waiting for that.

[@immagod5285]

💙❤️

[@nagathreyasharma]

thanks for the video, Please provide the sample data file for this, that would be helpful!

[@serenemary873]

2:18 Xlookup 4:00 advanced scenarios

[@rahulchopra2030]

Sir how can we make xlookup a part of our daily excel work

[@spyxxx566]

For annoying date issue, can we pre-formate those cells ahead so it will output the correct format we wanted?

[@chandoo_]

Yeah, that is the workaround for this issue. In future Excel might automatically format data based on source settings.

[@ublogs]

i have a question. there are two tables (created with ctrl + t command) on the same sheet in first table there are two headings 1. subject 2. marking type (means particular subject will be awarded grades or marks) (for example there are four subjects i.e. ENGLISH , MATH, DRAWING, SPORTS, MUSIC) [FIRST TWO SUBJECTS WILL BE AWARDED MARKS AND REST OF THREE WILL BE GRADED] now there is second table having one column 1. grading subject aim of creating second table is to show only grading subjects from ist table WHICH ARE DRAWING SPORTS AND MUSIC XLOOKUP NOT WORKING IS THERE ANY OTHER OPTION?

[@chandoo_]

You can use FILTER in this case. =FILTER(table[subject], table[marking type]="Grading") should work.

[@yesiunderstand6044]

We hope we will get a recorded version later

[@chandoo_]

You can watch it anytime on KZbin...

[@kristinemurray6476]

Hi Chandoo I use Vlookup all the time for data not in a table; why would we use xlookup instead of a vlookup?

[@chandoo_]

Hi Kristine.. do check this video where I explicitly compare V & XLOOKUP and show you more reasons to try this formula - kzbin.info/www/bejne/enXPi4uOeq5kmLc

[@anilkumarkarimbanakkal5043]

I am using excel 365 however I am unable to getting Xlookup function.. How can I enable it?

[@chandoo_]

Hi Anil... You may be on semi-annual update cycle for Office 365. You can change the preferences from Home > Account page in Excel. Alternatively, as per MS website, semi-annual channel people will get XLOOKUP in July 2020, so you can wait.

[@anilkumarkarimbanakkal5043]

@@chandoo_ Oh, great.. Thanks again

[@soundlee3399]

6:53 : Entire row

[@jucedica1]

How to search for a record with more than 3 results since with searchx you can search from the first to the last and from the last to the first

[@chandoo_]

You can use FILTER for that. See this video for more details - kzbin.info/www/bejne/gKa3lZ1on5Jsgck

[@isuruwickramasinghe9119]

U looks like Navajot sing sidu the crickter

[@chandoo_]

That is a new one. 😎

[@sahithikoluguri9539]

Xlookup waiting for in telugu Anna

[@ubaidillahmuhammad20]

amazing. share the file

[@chandoo_]

In the description

[@m.ahsaniqbal3218]

Heavy, heavy, very heavy

[@finnpj]

Good video but please - not so fast.

[@chandoo_]

Thanks for the feedback. I deliberately speeded up the video during edit. I will use normal speed next time.