10 Excel XLOOKUP Function Examples (Better than VLOOKUP & INDEX/MATCH)

In this video, I will show you what is the new XLOOKUP function and 10 XLOOKUP examples.

Note that this function is only available in Office 365 (Home, Personal and University edition) at the time of recording this video. It will likely be made available to all Office 365 users.

The following XLOOKUP examples are covered in this video:
1. SIMPLE LOOKUP (Lookup and fetch a value): In this example, I will show you how to use XLOOKUP to look for value and fetch it. It can fetch the value from the right or the left of the lookup value (something VLOOKUP isn't made to it)

2. LOOKUP AND FETCH THE ENTIRE RECORD: XLOOKUP can be used to look for value and fetch the entire record (entire row or column) from the return_array

3. TWO WAY LOOKUP: You can use two XLOOKUP functions together to get the two-way lookup (i.e, look for a value that meets two lookup criteria)

4. ERROR HANDLING IN XLOOKUP: Error handling in in-built in XLOOKUP and there is a dedicated argument where you can specify what you want in case there is an error. This could be a value, a cell reference or another formula. This a major improvement over using the VLOOKUP and IFERROR combination in the past

5. NESTED XLOOKUP: You can nest multiple XLOOKUP functions together to do a multi-level lookup. For example, you can look through multiple tables (in the same or separate worksheet) and fetch the value.

6. FIND THE LAST MATCHING VALUE: Again something that has been baked into XLOOKUP. You can choose the direction of the lookup. So you can fetch the first matching value or the last matching value

7. APPROXIMATE MATCH: There is also the approximate match where you can choose where you want the values just smaller/larger than the lookup value

8. HORIZONTAL LOOKUP: With XLOOKUP, you can do vertical lookup as well as horizontal lookup

9. CONDITIONAL LOOKUP: You can combine XLOOKUP with other formulas to do a conditional lookup. For example, if you want to look up what person has the maximum salary, you can do that easily using XLOOKUP with MAX.

10. WILDCARD LOOKUP: XLOOKUP can handle wildcard characters, but you need to specify that you will be using these. This example shows how to can do a partial lookup with wildcards.

⚙️ Gear I Recommend:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

[kenwilding]

Thank you! The previous videos on the same subject I just watched were unintelligible. Your explanation was much clearer and easier to understand.

[savissm]

I can not express how much I appreciate your work, Thank you very much Sumit !

[ExcelAndAccessGlobal]

Best video on the XLookup that I have seen. A must see. Honestly, I love all of his videos. But this one is my favorite of all of his work.

[kukanahoku]

I’m learning excel and you explain everything to clearly! Thank you. I haven’t found a video yet that shows how to pull data from a different sheet using xlookup. Trying this tomorrow! Subscribed to learn more 😊

[virex]

Your communication skill and teaching skill both are excellent

[ajvx]

Very helpful, and very clear. I am going to start converting some VLOOKUPs right away because I can see X removing quite a few potential issues down the road.

[preetis.]

OMG.. You made my work a breeze. I had wanted to find a formula which would return the most recent instance of lookup value and couldn't find any video which explained it. Thought would have to sort all my data in descending order to get it but that would mean sorting each time I added to the table 😅. This video gave me exactly what I wanted without messing with the order. Thank you so very much!!

[vivianpineda]

This is new for me. I'm thankful to see your video as i learn one new option today.:)

[MrTrD]

Finally I got it to work. I created a new sheet and then it worked. thanks. Can you please go slower next time, and don't assume people know where you click. Never assume.

[patmartin]

Sumit, What an awesome video about the fabulous new XLOOKUP excel function. Thank you SO MUCH

[dirkl]

Thanks for the clear and detailed explanation of the Xlookup function

[freddmello]

Very clear and very well explained, absolute beauty!

[roberth.]

Nicely done, Sumit. Thank you for the instruction.

[a.achirou]

Waou !! Amazing presentation. Thank you for your great explanation.

[nilimafreelancer]

Well explained with different examples. Thank you

[Datbrowngirl]

a beginner in excel and wow!!! xlook up is made easy! thanks you!

[RamKumar-vbet]

Amazing video - the best on this topic. Thanks for posting it.

[tahirnazir]

Great Work..4E... excellently explained entire entries

[naveenmedishetty]

Wow. Great function. Very nice explanation. Thank you so much

[shankarrao]

Excellent information about xlookup function thankyou very much sir.