Ultimate XLOOKUP Guide: 10 Tips You Need to Know!

[@B1897forzajuve]

XLOOKUP is a mandatory function if you like to work efficiently in Excel. It is easy to learn all its applications and so useful. Thank you Mark for these nice explanations.

[@kebincui]

The best and comprehensive video about the use of Xlookup function. Thanks Mark for your excellent work 👍❤

[@ExcelOffTheGrid]

Thanks Kebin. Glad you enjoyed it! 😁

[@DataVisualisation]

... and again learned something new / refreshed something ... RANGE function ... I hadn't considered using this XLOOKUP combination before. THANK YOU Mark

[@ExcelOffTheGrid]

You're welcome - I'm glad I could jog your memory of range functions.

[@IvanCortinas_ES]

Excellent in-depth review of the case studies of the function. These are real cases that appear in everyday life in the company. Thanks for sharing, Mark.

[@ExcelOffTheGrid]

Glad it was helpful! Thanks for watching.

[@PaulEBrownbill]

I like the vertical and horizontal lookup explanation, very easy to follow, thanks

[@ExcelOffTheGrid]

Glad it was helpful!

[@paramveerssachdeva]

Just when I think, I'd know it obviously and you show something new. Thanks a lot. The only X Id like to really lookup would be in Excel only.

[@Acheiropoietos]

An excellent summary. I often use Excel with Power BI and some of these combinations will be very useful! ❤

[@ExcelOffTheGrid]

Glad it was helpful!

[@williamarthur4801]

Great to see you mention that it can return a range by use of a colon, BTW really like the vid on a running total using makearray; I can see how it works, but would never have come up with it.

[@ExcelOffTheGrid]

Glad you enjoyed it 😁

[@omarnader776]

Thank you so much for your amazing and easy explanation

[@ExcelOffTheGrid]

Glad it was helpful!

[@daleanderson5258]

Great video Mark. Never knew all that was possible with XLookup.

[@ExcelOffTheGrid]

Glad you liked it! 😁

[@Nikamum]

Great uses for XLOOKUP, thank you for your teachings

[@ExcelOffTheGrid]

You're very welcome!

[@joukenienhuis6888]

Thank you for the explanation of this very clear function. I find it all very obvious, except fr the last example and looking up in a range. But now i think i get it.

[@ExcelOffTheGrid]

The 2 way lookup is a bit of a 🤯 But if you work through slowly it does make sense... I hope.

[@spen2431]

Excellent. Thanks for sharing 💥💥💥💥

[@ExcelOffTheGrid]

My pleasure - thanks for watching.

[@rajeshmajumdar4999]

Thank you so much!

[@ExcelOffTheGrid]

You're welcome!

[@Phamousman]

Some great tips for using XLOOKUP

[@ExcelOffTheGrid]

Glad it was helpful!

[@chrism9037]

Excellent video Mark, super helpful! One question: in example # 2, what was the purpose of "-" ? I tried it without that and just using the ampersand on the lookup values and an ampersand on the lookup arrays and it seemed to work fine. I might be missing something (?)

[@ExcelOffTheGrid]

Thanks Chris. Let's suggest you've got lookup values of AA | AAA And you're lookup array has 2 columns as follows from columns with: AAAA | A AAA | AA AA | AAA A | AAAA Without the "-" it will match against all of those values. So we include a spacer character to ensure it only matches against the correct row. The spacer character can be anything which is not found in your data set. It's very much a belt and braces approach, but something I've done for the past 20+ years.

[@chrism9037]

Ah, got it. Thanks Mark

[@andrewloosai1]

it's easy to upstanding, thx for sharing!!!

[@ExcelOffTheGrid]

You are welcome! 😁

[@aliab2201]

I wish I could like this video 100 times. ,👍👍👍

[@huseyinburaktasci1638]

Thank you!!

[@extraktAI]

Love it, amazing!

[@filipalm7585]

Excellent

[@tibibara]

Thanks Mark! (I somehow missed the regex match release, and now I'm amazed :D :D :D :D )

[@ExcelOffTheGrid]

That's interesting. To date I've never had much use for RegEx, so I've not got too excited by it. But you never know, that might change and I'll love it. 😁

[@tibibara]

@@ExcelOffTheGrid yes ;-) - I just tried to gather the most common patterns into a 'giga-mega' LAMBDA function, kinda 'Swiss army knife' formula, and I was curious how many characters the native name manager can handle 🤣🤣🤣 I was quite surprised by the result. But basically as you wrote, in practice I also used it very, very rarely... the implementation of XLOOKUP and XMATCH surprised me, but I don't see its practical use yet.

[@Dharm-o3i]

Two way lookup was good. , 👍

[@ExcelOffTheGrid]

Thanks - I hope you can put it to good use. 😁

[@Michael_Alaska]

Great tips. I was hoping you would explore the REGEX match in XLOOKUP.

[@ExcelOffTheGrid]

We've survived 40 years without RegEx in Excel, so I'm struggling to get excited by it. Maybe I'll have a solid use case for it one day... (then the video will come. 😁)

[@seaman4798]

Thank you for the video. Regarding Multi-Column Lookup. Why not use simplier code -- F7 & G7 instead of F7 & "-" & G7 ?

[@ExcelOffTheGrid]

Because without the space, it can lead to the wrong result. If our lookup_range has the following values ColA ColB A1 | 11 A11 | 1 What happens if our lookup is "A11"&"1"? It will match against the first row - which is wrong. But if you we a spacer character, it creates "A11-1". Therefore it can only match against row 2. Which is the correct result. Therefore, anytime I combine columns for comparison, I always include a spacer character which does not appear in the data. It prevents potential errors.

[@seaman4798]

@@ExcelOffTheGrid Thank you!

[@karinawijfje6562]

Great thanks, in example 7 Is it also possible to look up the newest date with an xlookup function regardless of the order of the file?

[@linlee79]

Great video! Is there a way to use Xlookup to return an type of format in a cell? For example, I have an employee number M111 in column A. There are two results for this employee in column B, hours and an amount (hours multiplied by rate). I only want to return the amount in column B, I do not want to see the hours.

[@ExcelOffTheGrid]

Use TEXTBEFORE or TEXTAFTER to split the values in the cell. Between the two values. The bigger question is, why have you got two values in a single cell? Sounds like you’ve got a data issue which will cause you a lot of problems in the future.

[@JoséMoratoCarvalho]

Dear Mark. In the "2-Multi-column lookup" example, you can also use the following method: =XLOOKUP(1,((F7=Example2[Item])*(G7=Example2[Size])),Example2[Value],0) 🤗

[@ExcelOffTheGrid]

You can use 1, but I’m pretty sure it only works for an exact match. Where I believe combining values still allows the other match_modes to work.

[@B1897forzajuve]

More efficient than the method with & for sure. I would use the one with & only with a small lookup base.

[@stevenflax]

Thanks for crystal clear review. Is it possible to combine #3 (scalar lifting) and #8(return multiple value)? When I tried my result is limited to one column result

[@ExcelOffTheGrid]

That's the array of arrays issue. The #8 (return multiple values) is an array and not single values, so when you apply #3 (scalar lifting), you are creating an array of arrays. Excel needs an array of horizontal & vertical values, not an array of arrays. If we use the data in #3, there are lots of ways we could solve that - here are 3 options. #1 =DROP(CHOOSEROWS(Example3,XMATCH(F7:F10,Example3[Item])),,1) #2 =DROP(REDUCE("",F7:F10,LAMBDA(a,v,VSTACK(a,XLOOKUP(v,Example3[Item],Example3[[Size]:[Value]])))),1) #3 =TAKE(SORTBY(Example3[[Size]:[Value]],XMATCH(Example3[Item],F7:F10)),ROWS(F7:F10))

[@stevenflax]

Thanks!!! Been stuck on this for a while....can't wait to try your proposed solutions!!

[@leerv.]

I had to try playing around last night after watching this video, and noticed the same thing. My solution was to make the lookup value and lookup range both absolute, and then just drag the formula to the right. Works great! To clarify I mean setting the first and second arguments of the XLOOKUP to absolute references

[@leerv.]

#3 ... what the heck?! Nobody told me this!! Well, until now! Thanks, Mark! Question: do you think this would be more performant than the standard one-formula-per-row approach, or just more convenient?

[@ExcelOffTheGrid]

Like most things, it depends. From my general testing on arrays a few years ago I would say that: The spilling method is slightly faster for < 10,000 rows The methods are similar for 10,000 - 50,000 rows The individual rows is slightly slower for > 50,000 rows But the convenience benefit is significantly bigger than any performance issue.

[@leerv.]

@@ExcelOffTheGrid thank you! I'm showing this to peers today ☺️

[@ExcelOffTheGrid]

That's great 👍

[@ismaelkourouma5558]

Great Mark. Quick question: If you lookup multiple instances like this formula: =XLOOKUP(A4:A6,M4:M1323,N4:O1323), XLOOKUP returns only a single column in this case the column N, not an array. But, if you use XLOOKUP like this by choosing a single column as lookup value: =XLOOKUP(A4,M4:M1323,N4:O1323) then you drill down the formula, it returns for each line two columns as return array. This is bit strange since the first formula work perfect when you select only a single column as lookup value (in this case it returns a array). Any idea? Thanks

[@ExcelOffTheGrid]

This is the array of arrays issue. Just wait for the video in 2 weeks and all will be clear.

[@hiliks50]

Hi. I have watched this video and on the part of the wildcard search I have a question or two. 1. on your list there are 3 cell with *456* . When you make the XL search you get the 1st occurrence of 456... but there are other 2 cells with 456. I know you can enter the search mode but on either case it will give you the 1st or last occurrence. what if I have a list with 14 cells with the same occurrence but diff values in total?

[@ExcelOffTheGrid]

The search_mode in XLOOKUP only gives you first or the last - they are the only options. If you want the nth match, the easiest method is the FILTER to get all the matches, then use CHIOSEROWS to get the specific row.

[@87CVH]

I would write it differently Xlookup(rowvalue,rows, Xlookup(colvalue,columns,values))

[@RakshitDwivedi-pq8qc]

Hi Mark, in the 3rd function you show that X- Look up getting multiple values with the formula spilling downwards. but can it spill both ways at the same time. i have been trying to use it in my own office work and alongside choosecols function i am able to get it to spill horizontally but not vertically at the same time. is there any solution for it? Looking forward to your response.

[@ExcelOffTheGrid]

Yes. It's because doing that with XLOOKUP will cause the array of arrays issue. Here are 3 possible solutions to spill in both directions: #1 =DROP(CHOOSEROWS(Example3,XMATCH(F7:F10,Example3[Item])),,1) #2 =DROP(REDUCE("",F7:F10,LAMBDA(a,v,VSTACK(a,XLOOKUP(v,Example3[Item],Example3[[Size]:[Value]])))),1) #3 =TAKE(SORTBY(Example3[[Size]:[Value]],XMATCH(Example3[Item],F7:F10)),ROWS(F7:F10))

[@RakshitDwivedi-pq8qc]

@@ExcelOffTheGrid amazing. Thank you so much. Will definitely try this and let you know.

[@notaras1985]

If you want to have the most recent tools like python and AI etc, which specific subscription should you have? Thanks for your time.

[@ExcelOffTheGrid]

Basic Python requires Excel 365. But if you want the full Python or AI there are premium licences you need to purchase in addition. Copilot is an additional $20 per month: www.microsoft.com/en-us/microsoft-365/copilot Full python is an additional $24 per month: www.microsoft.com/en-us/microsoft-365/python-in-excel It starts getting expensive.

[@jenniferdustin8214]

So we can use Xlookup/Xlookup, instead of Index Match? Is it faster, or just easier to understand?

[@ExcelOffTheGrid]

There is also INDEX/XMATCH/XMATCH as another option. Personally I find XLOOKUP/XLOOKUP is more confusing than INDEX/XMATCH/XMATCH. In terms of speed… as always… it depends. Ultimately no significant difference either way.

[@financnifitness2583]

Hi, great! Actually in the multi-column --> it is actually just enough to make it only with & --> XLOOKUP (F7&G7;......)..gives the same :) . Also, how did you make in wildcard example that you do not see in the cell the apostrophe, only you see it when you are in the cell.

[@ExcelOffTheGrid]

Well... actually F7&G7 without the spacer character can be a little risky, as it can accidentally match against other combinations. But using a spacer character that doesn't appear in the data, guarantees that this doesn't happen. In regards to the apostrophe... no idea. Excel just did what it does. I didn't do anything special.

[@Mark_Lacey]

Why not preface the video with the fact XLOOKUP only works in Office 365?

[@ExcelOffTheGrid]

It also works in Excel 2021 and Excel 2024. Based on the stats I've seen, the vast majority of users will have access to it. So I think we are well past the point of stating which versions it relates to.

[@Mark_Lacey]

@@ExcelOffTheGrid Fair enough, thanks.

[@mattbowden1981]

Isn’t 365 the latest version?

[@AReza-kt6fj]

It works perfectly in my Excel 2021.

[@brianxyz]

@@mattbowden1981 Yes, but the channel you're on will impact when you get new functions.