STOP using INDEX MATCH in Excel, use this instead!!!

Excel's INDEX+MATCH formula is a staple for many. But do you know that Excel now has a simple alternative to this powerful formula combination? Yes, I haven't used INDEX MATCH function in over a year and here is why.

👉 XLOOKUP 👈

The magic new function can do what INDEX+MATCH does with a simpler syntax and offers many extra features. In this video, I will show you how you can replace your messy INDEX+MATCH formulas with simpler XLOOKUP variation.

Timestamps:
===========
0:00 - Introduction
0:09 - XLOOKUP overview & demo
1:10 - INDEX MATCH replacement with XLOOKUP
2:54 - Dealing with errors & If not found option in XLOOKUP
3:40 - One more example of XLOOKUP
4:09 - Advanced use of XLOOKUP - showing all details for matching values
5:35 - Closing remarks

Example file + more on XLOOKUP:
===========================

👉Checkout my online course on Advanced Excel & Data analysis
===================================================

#XLOOKUP #ExcelTricks

[wayneedmondson]

Hi Chandoo. Nice one! In cell K19, I wrapped XLOOKUP in TRANSPOSE to get the returned array of data vertically instead of horizontally, as in: =TRANSPOSE(XLOOKUP(J19, G6:G21, C6:F21, "no such person")). XLOOKUP is awesome! Thanks for sharing your tips :)) Thumbs up!!

[matchanista]

I know this was two years ago - but I tell you THIS is the only video that explained it so well, you are my hero!!!

[frenan]

Thanks for the Tip. If the day starts with Chandoo video, It starts very well. Have a good day.

[dakshbhatnagar]

This is an amazing replacement. No complex functions and reasoning how everything came together to make that function work. Thanks a lot, man.

[kashmoney]

Thanks bro...I also use xlookup within xlookup formula eliminates using other limitation lookups ...also you do 2-3 lookups at a time! 🔥

[suraj]

Great. I am student of excel school and power pivot. This is very useful

[babu]

G8...easy....short....simple teaching method....well done

[gjairaj]

Thank you Chandoo Garu, the way you explain was really good and easy to understand the concept on X Lookup

[laniefigueroa]

You're the man! You made it look so simple. Thank you very much!

[anv.]

Thank you. Chandoo. Well appreciated the way you have teached.

[vloveg]

I have not yet found an alternative to search on 2 factors, other than INDEX MATCH. In this example, we had different starting pay for different jobs levels at different locations. I indexed the array, then match the location to the row labels and match the job levels to the column labels.
Maybe I will find a simpler solution in your other videos.
I am definitely excited, now that XLOOKUP is available in the desktop version of Excel!

[behzadghah]

You're amazing teacher Chandoo, thank you❤🌹🙏

[kaygana]

Hi Chandoo... would you please post a video on preparing for the Microsoft Excel Certification Test? Thanks in advance

[Tanya-os]

Hi Chandoo. Very well explained. Thank you so much. 👍

[Aleccsandru]

Hello! Just a quick question- what happens if you have the same salary for two persons. What formula to use ?

[TheDistractionStudio]

Omg!! You are a Magician. Love this new formula. Thank You so much Sir.

[ATInstitute]

That means Xlookup can me use to check Information pertaining to certain Information.

For example, if you have an iD number, it can be key in, and it will generate all details concerning the person

[DenisVintage]

That's a good one... Thanks Chandoo! See ya on Mar 26!

[Excelambda]

Great Video!! XLOOKUP rocks. Also we can use different match modes of XLOOKUP, next smaller, next larger.
Or to filter the closest in absolute value (whatever is smaller or larger than the lookup value)
=FILTER(C6:G21, ABS(K19-G6:G21)=MIN(ABS(K19-G6:G21))) for 74775 filters 2 results because is exactly in the middle of 74550 and 75000, so their abs dif is equal.

[lewisgottschalk]

Another great lesson from Chandoo. Thanks.