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.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 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.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Пікірлер: 82
For more videos that cover lookup concepts see the playlist at kzread.info/head/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l
@DougHExcel
3 жыл бұрын
...
this is what I have been looking for ages. Thx man!
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!
Doug, you da man! Thanks so much for the clear explanation. I'm going to practice this now! :)
@DougHExcel
7 жыл бұрын
Hi rockguitarist8907, glad you liked it, thanks for commenting!
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
6 жыл бұрын
Hi Bradza101, glad you liked it, thanks for commenting!
Thanks for teaching, not only the function but also how it works... Thanks man🙏
@DougHExcel
3 жыл бұрын
No problem 👍
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
4 жыл бұрын
Glad I could help
Thank you so much! I appreciate your explanation.
@DougHExcel
5 жыл бұрын
Hi Angelina Sepulveda, glad you liked it, thanks for commenting!
I will be utilizing this to create a Maintenance Schedule for my mechanic at my business. Thank you so much!
@DougHExcel
3 жыл бұрын
You’re welcome!
Good explanation. Understood the method. Thank you very much.
@DougHExcel
Жыл бұрын
You are welcome!
THANK YOU SO MUCH, YOU SAVE ME
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
3 жыл бұрын
Hi Aref Alkadi, glad you liked! Thanks for the subscribe; there are a bunch of these videos and I have new ones published weekly.
Much appreciated. Easy solution thanks.
@DougHExcel
2 жыл бұрын
You’re welcome!
Thanks a ton. It helped
@DougHExcel
2 жыл бұрын
Glad it helped!
this does help me! thanks!
@DougHExcel
7 жыл бұрын
Thanks Matthews M. Panjaitan, glad it helped!
Sir can you help me if the last value is 0 then how can we have to find the second last value
Luv it!!!!
@DougHExcel
5 жыл бұрын
Thanks for the comment!
Awesome. Thanks
@DougHExcel
4 жыл бұрын
You're welcome!
Thank you very much
@DougHExcel
3 жыл бұрын
Any time!
Thank you so much
@DougHExcel
2 жыл бұрын
You’re welcome!
Use full thanks lot
@DougHExcel
Жыл бұрын
Hi dhavamani krishnana, thanks for the comment!
Thank you sir
@DougHExcel
Жыл бұрын
You’re welcome!
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
6 жыл бұрын
Try this vid for some insight --> kzread.info/dash/bejne/qG2fmqWoerSToJM.html
it sure did help
@DougHExcel
Жыл бұрын
Glad it helped!
Thank you
@DougHExcel
Жыл бұрын
You're welcome
Helpful
@DougHExcel
6 жыл бұрын
Hi demon is watching, thanks for the comment!
This is really helpful I was try this for last one week but i failed Now i got it
@DougHExcel
5 жыл бұрын
Glad it helped!
Thanks
@DougHExcel
Жыл бұрын
Welcome!
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
6 жыл бұрын
You're Welcome!
Is it working if your H1 is in another sheet? And your range is having blank cells?
@DougHExcel
3 жыл бұрын
should work, and on the other question it depends on blanks cells locations.
What if I want to find the max value for Item 3 instead of the last value? What formula I should use?
@DougHExcel
18 күн бұрын
See if this fits. Use VLOOKUP to Find the 2nd Match (or nth Match) kzread.info/dash/bejne/qG2fmqWoerSToJM.html
@securityinvestigator2095
18 күн бұрын
@@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
that's really great, can i ignore zero numbers? i mean if the last number is zero take before that number?
@DougHExcel
3 жыл бұрын
Maybe you'd want to pick and choose from a list with this solution >> kzread.info/dash/bejne/qG2fmqWoerSToJM.html
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
3 жыл бұрын
may need to change the cell formatting
Genius
@DougHExcel
2 жыл бұрын
Hi Lord Stark, thanks for the kind words!
This is useful, How to lookup the last non blank matching value?
@DougHExcel
2 жыл бұрын
One of these might help kzread.info/dash/bejne/lquL16qBlLvVkqQ.html kzread.info/dash/bejne/qG2fmqWoerSToJM.html
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
3 жыл бұрын
Yes it should
I was hoping it would work in Google Spreadsheet. But it isn't. Can anyone help!
@DougHExcel
3 жыл бұрын
Hi Roarz A, thanks for the comment! But don't know google sheets...
man , you just save my ass
@DougHExcel
4 жыл бұрын
Thanks ابو هلال الهلالي, glad it helped!
this code =LOOKUP(2,1/(B:B=E18),C:C) is not work in google sheet why????
@DougHExcel
5 жыл бұрын
+NAVEEN SINGH sorry don’t know about google sheets
@deryrahmanahaddienata9428
3 жыл бұрын
Try this =ARRAYFORMULA(LOOKUP(2,1/(B:B=E18),C:C))
Complicated
Thank you sir
@DougHExcel
4 жыл бұрын
You're Welcome!