Lookup the Last Matching Value

If you are familiar with lookup function in Excel, it brings back the first value it finds in your lookup table. What if you had multiple values but wanted to find the last (or maybe it is the most current) value from your table? You could do some table sorting (like sort descending on some criteria like date) and then let the lookup function do it or use can use an "older" lookup function in Excel aptly named LOOKUP to do this for you. You do need to perform some calculation first and then have it wrapped in the lookup function. It's actually not that hard so check out the video.
🔔 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...
~-~~-~~~-~~-~

[@DougHExcel]

For more videos that cover lookup concepts see the playlist at ua-cam.com/play/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l.html

[@DougHExcel]

...

[@nunyabusiness4516]

I was looking for a solution and this is by far the easiest one I have found. Thank you so much for taking the time to share this!

[@DougHExcel]

Glad it helped!

[@pragha_]

Thanks for teaching, not only the function but also how it works... Thanks man🙏

[@DougHExcel]

No problem 👍

[@RyanRusydaTresnatriadi]

this is what I have been looking for ages. Thx man!

[@Bradza101plays]

been looking for help on this. found many people giveing this answer, but unlike the others you explained why and how it works. SUBED

[@DougHExcel]

Hi Bradza101, glad you liked it, thanks for commenting!

[@slyassassins202]

I will be utilizing this to create a Maintenance Schedule for my mechanic at my business. Thank you so much!

[@DougHExcel]

You’re welcome!

[@Hari-n4f]

Good explanation. Understood the method. Thank you very much.

[@DougHExcel]

You are welcome!

[@arefalkadi3685]

What a simple formula but very effective for I wanted, I have tried several Index and Match to get the last updated data of a reoccuring item and I could not, until I sow your video. I subscribed immediately.

[@DougHExcel]

Hi Aref Alkadi, glad you liked! Thanks for the subscribe; there are a bunch of these videos and I have new ones published weekly.

[@siddhantchopra1]

Thanks, Thanks a lot brother, I have been searching for this since 1 month tried so many things nothing worked.
Great work!
It was really helpful :)

[@DougHExcel]

Glad I could help

[@patrickpellegrini8681]

Much appreciated. Easy solution thanks.

[@DougHExcel]

You’re welcome!

[@enochjoshua03]

Thanks a ton. It helped

[@DougHExcel]

Glad it helped!

[@rockguitarist8907]

Doug, you da man! Thanks so much for the clear explanation. I'm going to practice this now! :)

[@DougHExcel]

Hi rockguitarist8907, glad you liked it, thanks for commenting!

[@rockguitarist8907]

Doug one thing I noticed. You say in the vid that the lookup value looks in the array for the closest number below the lookup value. I used =LOOKUP(2,(--($A$2:$A$6="Kyle")),$B$2:$B$6) and hit Ctrl + Shift + Enter, but I got the last value instead of the last match for "Kyle". My array in the lookup array was {1;0;1;1;0} So I figured my result would come from the row of the last "1" from the array (the last TRUE). But I was getting the LAST value of the table, which was NOT a match. I watched your video again and you used a "/" to make the zeroes becomes errors. My reason for posting this is for anyone else that tries this, you have to do it Doug's way. Use the "/" to convert the FALSE to an error rather than "0". Otherwise, you will return the final value. Thanks Doug!

[@DougHExcel]

You're Welcome!

[@angelinasepulveda2160]

Thank you so much! I appreciate your explanation.

[@DougHExcel]

Hi Angelina Sepulveda, glad you liked it, thanks for commenting!

[@windyfebbiayuni5581]

THANK YOU SO MUCH, YOU SAVE ME

[@pathwayToData]

This is really helpful
I was try this for last one week but i failed
Now i got it

[@DougHExcel]

Glad it helped!

[@enasswerfally6264]

This is useful,
How to lookup the last non blank matching value?

[@DougHExcel]

One of these might help
ua-cam.com/video/dwWuHHdXobA/v-deo.html
ua-cam.com/video/v9k8CoJQ-p0/v-deo.html

[@bokooo3129]

it sure did help

[@DougHExcel]

Glad it helped!

[@unknownboy3828]

Sir can you help me if the last value is 0 then how can we have to find the second last value

[@Iahmedmcse]

that's really great, can i ignore zero numbers? i mean if the last number is zero take before that number?

[@DougHExcel]

Maybe you'd want to pick and choose from a list with this solution >> ua-cam.com/video/v9k8CoJQ-p0/v-deo.html

[@dhavamanikrishnana2226]

Use full thanks lot

[@DougHExcel]

Hi dhavamani krishnana, thanks for the comment!

[@abdanomer]

Great idea...
So what if i want the second value or the "n" value instade of the last one!
Small or large could be use with the lookup?

[@DougHExcel]

Try this vid for some insight --> ua-cam.com/video/v9k8CoJQ-p0/v-deo.html

[@securityinvestigator2095]

What if I want to find the max value for Item 3 instead of the last value? What formula I should use?

[@DougHExcel]

See if this fits. Use VLOOKUP to Find the 2nd Match (or nth Match)
ua-cam.com/video/v9k8CoJQ-p0/v-deo.html

[@securityinvestigator2095]

@@DougHExcel The helper table can help me get the last value for a specific cell but not the max value. It seems like I may need a 'sortby' function within xlookup to get the max value

[@srsaravanakumar]

Thank you

[@DougHExcel]

You're welcome

[@wilfredocabuenas]

Is it working if your H1 is in another sheet? And your range is having blank cells?

[@DougHExcel]

should work, and on the other question it depends on blanks cells locations.

[@nexttechreview9248]

Awesome. Thanks

[@DougHExcel]

You're welcome!

[@MatthewsMPanjaitan]

this does help me! thanks!

[@DougHExcel]

Thanks Matthews M. Panjaitan, glad it helped!

[@feroz881]

Thanks

[@DougHExcel]

Welcome!

[@anshulbharti683]

Thank you sir

[@DougHExcel]

You're Welcome!

[@mudassiralirana4256]

Please tell me if there is a value in nagetive how can I show it ? This formula works perfectly but does not show if a nagetive value arrives ...

[@DougHExcel]

may need to change the cell formatting

[@cleitonoliveira932]

Thank you! If someone still sees this, it's possible to get the ADDRESS in that same scenario? I need the address, not the value and no success.

[@DougHExcel]

Yes it should

[@complex58]

Luv it!!!!

[@DougHExcel]

Thanks for the comment!

[@spuneet156]

Helpful

[@DougHExcel]

Hi demon is watching, thanks for the comment!

[@tejaswini666]

I was hoping it would work in Google Spreadsheet. But it isn't. Can anyone help!

[@DougHExcel]

Hi Roarz A, thanks for the comment! But don't know google sheets...

[@naveensingh3785]

this code =LOOKUP(2,1/(B:B=E18),C:C) is not work in google sheet why????

[@DougHExcel]

+NAVEEN SINGH sorry don’t know about google sheets

[@deryrahmanahaddienata9428]

Try this =ARRAYFORMULA(LOOKUP(2,1/(B:B=E18),C:C))

[@lordstark806]

Genius

[@DougHExcel]

Hi Lord Stark, thanks for the kind words!

[@algamdie]

man , you just save my ass

[@DougHExcel]

Thanks ابو هلال الهلالي, glad it helped!

[@pravinsambe1648]

Complicated

[@raidmahbouba]

Thank you very much

[@DougHExcel]

Any time!

[@naif9380]

Thank you so much

[@DougHExcel]

You’re welcome!

[@richardgoone3438]

Thank you sir

[@DougHExcel]

You’re welcome!