Lookup the Last Value in Excel [XLOOKUP & INDEX / MATCH]

Looking up the last value is usually not the default when you perform lookups with various lookup functions. However there are times when you want to find the last item in a list or array that matches a criteria. With the newer XLOOKUP function, it makes this job very easy. The only catch is that XLOOKUP is on newer version of Excel. If you didn't have the latest Excel version, you can still perform a lookup for the last item and that can be done with a combination of the INDEX and MATCH functions.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

[@wayneedmondson1065]

Nice one Doug! For the old method, have to remember to enclose the 1/(match test) in parenthesis or it will fail. Thanks for the reminder. XLOOKUP makes it so much easier! Thumbs up!!

[@DougHExcel]

Thanks Wayne, XLOOKUP is great!

[@howardgerbitz1443]

How would find the last value, if Items contained Item 1 and Item1A and you wanted the last value with either Item1 or Item 1A?

[@TVSCDN]

How to get last value of an item in say a Grocery item purchased with my present billed rate of my Grocery😊?? What will be my XLOOKUP FORMULA...?? so I can ask my Grocery shop why have you billed me Extra for the same item??

[@trixiegtan]

Very helpful. Thank you for sharing

[@DougHExcel]

You’re welcome!

[@llaw785]

Great comparison between the 2 methods, thanks. Does that mean I can safely put Index/Match to peace, for this is slightly more workaround to achieving same results? Also, what happens if I send my workings to folks who don't have 365 will the formula break?

[@DougHExcel]

If you’re not expecting stakeholders to collaborate and update then XLOOKUP is the way to go when you don’t know if they have M365. Otherwise it’ll be the INDEX/MATCH method.

[@llaw785]

@@DougHExcel But at least the formula won't break or just be seen as error @#*!?

[@giulian7777]

Looking for a way to use the xlookup or some function to return the 2nd to last value, or 3rd to last value, etc. Any recommendations?

[@DougHExcel]

Maybe this one Use VLOOKUP to Find the 2nd Match (or nth Match)
ruclips.net/video/v9k8CoJQ-p0/видео.html

[@sarkarji960]

Great, Thanks👍

[@DougHExcel]

Welcome 👍