Lookup the Last Matching Value

[@DougHExcel]

For more videos that cover lookup concepts see the playlist at kzbin.info/aero/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l

[@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!

[@RyanRusydaTresnatriadi]

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

[@pragha_]

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

[@DougHExcel]

No problem 👍

[@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!

[@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

[@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.

[@unknownboy3828]

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

[@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) kzbin.info/www/bejne/rGrOaXalf7ZgppI

[@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

[@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.

[@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 >> kzbin.info/www/bejne/rGrOaXalf7ZgppI

[@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

[@patrickpellegrini8681]

Much appreciated. Easy solution thanks.

[@DougHExcel]

You’re welcome!

[@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!

[@angelinasepulveda2160]

Thank you so much! I appreciate your explanation.

[@DougHExcel]

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

[@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 --> kzbin.info/www/bejne/rGrOaXalf7ZgppI

[@pathwayToData]

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

[@DougHExcel]

Glad it helped!

[@windyfebbiayuni5581]

THANK YOU SO MUCH, YOU SAVE ME

[@enasswerfally6264]

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

[@DougHExcel]

One of these might help kzbin.info/www/bejne/mqi6pnt-mb2imKM kzbin.info/www/bejne/rGrOaXalf7ZgppI

[@enochjoshua03]

Thanks a ton. It helped

[@DougHExcel]

Glad it helped!

[@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!

[@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))

[@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

[@bokooo3129]

it sure did help

[@DougHExcel]

Glad it helped!

[@MatthewsMPanjaitan]

this does help me! thanks!

[@DougHExcel]

Thanks Matthews M. Panjaitan, glad it helped!

[@nexttechreview9248]

Awesome. Thanks

[@DougHExcel]

You're welcome!

[@dhavamanikrishnana2226]

Use full thanks lot

[@DougHExcel]

Hi dhavamani krishnana, thanks for the comment!

[@srsaravanakumar]

Thank you

[@DougHExcel]

You're welcome

[@complex58]

Luv it!!!!

[@DougHExcel]

Thanks for the comment!

[@anshulbharti683]

Thank you sir

[@DougHExcel]

You're Welcome!

[@feroz881]

Thanks

[@DougHExcel]

Welcome!

[@spuneet156]

Helpful

[@DougHExcel]

Hi demon is watching, thanks for the comment!

[@algamdie]

man , you just save my ass

[@DougHExcel]

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

[@lordstark806]

Genius

[@DougHExcel]

Hi Lord Stark, thanks for the kind words!

[@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!