New Xlookup Function: A Vlookup Comparison

Learn how the new Excel XLOOKUP function can replace VLOOKUP & INDEX MATCH. Partial match, reverse order, and horizontal lookup formulas are now possible with this one function.

XLOOKUP can perform both vertical or horizontal lookups to return a value from a matching row or column. The function only requires 3 arguments and separates the lookup and return arranges for lookup formulas that are easier to read and write.

Here is the agenda for the video.

1. Overview of XLOOKUP. How to write the formula and an explanation of all 5 arguments. 0:56
2. Horizontal lookups 4:16
3. Partial Match with Wildcard Characters 4:56
4. Reverse Search Order 6:54
5. A brief preview of XMATCH 7:47
6. XLOOKUP vs. VLOOKUP 8:12
7. XLOOKUP to the Left 10:04
8. XLOOKUP Closest/Approximate Match 10:33
9. XLOOKUP vs INDEX MATCH 11:30
10. Pros or Advantages of XLOOKUP 13:01
11. Cons or Drawbacks of XLOOKUP 13:48
12. # VALUE! Error with XLOOKUP 14:20
13. VLOOKUP Assistant preview 16:02
14. Availability & Compatibility 18:48
15. Questions for you 19:49

Please leave a comment below and answer either or both of the following questions:

1. What do you think of XLOOKUP?

2. What other uses or problems are you wondering if XLOOKUP can solve?

Additional Resources:

Note: You will need a version of Excel that supports XLOOKUP for the formulas to work.

Thank you! 🙂

#ExcelCampus #MsExcel
00:00 Introduction
00:16 The New XLOOKUP
04:18 Horizontal Lookup
04:56 Partial Match with Wildcards
06:54 Search Order
08:13 XLOOKUP vs. VLOOKUP
10:34 Closest Match
11:31 XLOOKUP vs. INDEX MATCH
13:02 Pros & Cons of XLOOKUP
18:48 Availability & Compatibility

[sarahwilson]

Yes!! X-lookup is so much more logical

[AlThePal]

Funny how they added the if not returned part to this ;)

[letlotlonjoro]

Thank you, very simplified.

How do I get the vlookup assistance

[LanNguyen-vnou]

Thank you so much for the Video. may I ask how to add in Vlookup Assistant?

[Osiris]

14:21 The easiest way to avoid the range length error is by selecting the column instead of selecting a specific range. This also makes the formula easier to type and understand. For example, =XLOOKUP(A4, E:E, G:G) is way cleaner than =XLOOKUP(A4, E4:E1003, G4:G1003), and it catches everything in both columns instead of accidently leaving any data out.

[akashingole]

I feel like you have enhanced my overall intelligence by this video. Made me think some complex formulaes and scenarios in a understanding way. Thanks.

[rwhayes]

Hi, Xlookup is great. I will use it for searching for a formula based on what criteria i use. Thanks

[akinkazeem]

Nice lesson, will try it and learn it more

[asutoshnayak]

Nicely explained. I appreciate your effort 🙂🙂

[equiwave]

Hi Jon,

Thanks for this super useful video.

For the 2D Lookup to get the value for South Region and Mar Month I have used the below approach:
XLOOKUP(C3, $B$7:$M$7, XLOOKUP(B3, $A$8:$A$11, $B$8:$M$11))
I have used Nested XLOOKUPs.

Is this correct and acceptable?

Regards,
Deepak.

[ompaul]

This is really informative Jon, now i am well aware of the cons, big thanks

[raghavrungta]

your videos are very easy to understand and helpful. Thanks a lot

[padynz]

Thank you very much for a great video with comparisons.

[DRHades]

1. Yes
2. simplify my life

Excellent video

[UpForDebate-]

How could you combine horizontal and vertical look up with xlookup

[justinthibault]

Hi Jon. just stumbled on your account, great videos I have learned quite a bit! But I was wondering if you can help me with something? I Have been trying to use excel to track recipes and stock, Is there anyway I can have my inventory laid out, then my recipes and then use a formula to track amounts used with each recipe and keep a running total of the amount of each ingredient remaining? Thanks!

[JD-euld]

Thanks! I needed to see comparison of these three.

The latest xLookUp has the [Value if not found] argument which is very handy because I didn't have to add IfError just to avoid the #N/A return.

[MemeticsX]

Makes me wonder why XLOOKUP was not in Excel 10-20 years ago.

[vickyyoung]

if you use a named range, does that mean you no ;pnger need to use absolute cell references? xxx

[angelitageraldez]

All your videos are so helpful. Thank you soo much!