VLOOKUP to Find the Last Value in a List
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- Learn more incredible Excel Formulas - bit.ly/Ultimat...
Use VLOOKUP to return the last match in a list. The VLOOKUP function is brilliant, but it has its limitations.
One of these is that the VLOOKUP function can only find the first value in a list. This is great when you are looking for unique values, but not when the value occurs multiple times in a list.
This video tutorial shows how to use VLOOKUP to find the last value in a list.
In the video, the COUNTIF function is used to find how many times the lookup value occurs. It is also used to create a column of unique values.
View 5 alternative reasons to use the COUNTIF function
• 5 Alternative Reasons ...
The VLOOKUP function is then used on the column of unique values.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
After going through alotta forums and even the excel's official site and lots of tutorial and only getting some basic stuff, at last i found what i wanted here, Respect
Glad I could help!
Ty for the help.
I applied this formula to find the last payment date of the customer in a big data, it works. Thank you!
Fantastic! Happy to help Wilfredo.
It's very amazing idea . Thanks
Thanks and welcome.
This is brilliant and a useful yet logical alternative to the lookup method.
Thank you.
very smart thinking
Thank you Prabhakar.
I have been trying to figure this out for years. Thanks!
Job done! :)
Thanks ❤
You're welcome 😊
Learn from your vids, applied to my job. Thank you so much
My pleasure My Nguyen.
Hi Alan.. excellent.. had not thought of your method to solve this problem. So simple, yet so clever. Outstanding! Thumbs up!
Thanks Wayne.
Thank you Sir 😊
You're welcome, Virender.
Thanks very easy and helpful !
Thanks for your help, it makes sense to create a unique identifier and work with that.
You're welcome. Thank you Martin.
@@Computergaga When I started tackling this issue, I was shocked to find that you cannot directly apply sort to the range parameter within the vlookup formula and basically sort it in desc, then get the first occurrence (the last before sorting). Ot at least that was my initial idea.
For sure. In Excel 365 the XLOOKUP and XMATCH functions offer a search last-to-first option. There is also a SORT function that can be used. But prior to that version, there are no built-in function options.
Thanks. Keep they coming.
Thank You very much. Finally can do it for my business sheet. 2 days I searching this solution and finally I get your video with smart and simple logic
You're welcome Vicky.
Gr8 Idea. Helped me today. Thanks...
You're very welcome Robert.
Good work... and well explained. 🖒
Thank you.
You are my hero!!! Thank you!!!
Thank you Deanna 😀
Great Job ! thank you very much sir.
Thank you.
still looking for one situation...in column a repeated apple 5 times in column b subsequently 12345
now i am looking to bring the last entered value from column b that is 5
seeking your help in this regards. thanks
Sounds like you may need another helper column. A repeat of the process in this video for the 5's. So that you can receive the last of the 5's.
THANKS SIR
You're very welcome Dulichand.
this is what i'm looking for..thanks mate!
You're very welcome Roy.
thank you good job
Thank you Jay.
Another interesting way to do this would be with a maxifs rather than countif, then all you would need in the helper column would be a series on numbers from 1 to k. Then use the formula = VLOOKUP(MAXIFS(HelperColumn,Criteia column, Criteria),Table_Array,Col_Index,FALSE)
Please could you show me how to do it? I need to extract the latest value from a huge list from certain part numbers, I don't want to introduce every time a value to concatenate and bla bla bla... thank you.
Excellent
Thank you Majid.
Nice work, very smart way. Thank a lot mate!
You're very welcome, thank you.
Is there a way to do this on a table? Every time I add a new row there is an unwanted cell target shift that doesn't match what would happen if I was to drag the function down. Any ideas?
Nice trick, can you avoid helper column and show the same, may be with index and match pls.
Thanks Wahab. Not sure about avoiding the helper column because the lookup functions will want something physical to look in.
An INDEX and MATCH version could look like this - =INDEX(F:F,MATCH(I4&J1,D:D,0))
nice trick
Cheers Amit
Hi. Great video, i actually used it to sort a problem.
But then decided it was to complex in formula for me to give to others at work to do each week.
Then one night i got a idea
Export all data from Myob as csv
Import into excel ( 40,000 transactions )
Sort 2 ways Firstly by Product code, then second level sort Date Newest to oldest
Then copy of of the main data part number and paste into new column, then highlight new column and remove duplicated ( multiple sales )
From this new smaller list of catalog part numbers i do a vlookup by part number and jump across 3 colums to the unit sales price
This gives me the last purchase price because the file is sorted by part number first then by date with newest on the top - so vlookup now gives me the first match as it does and it is the latest price sold - what i was looking for
I do use your formula for other items, first price, last price , second last price, then combine a sumif for the average - good for seeing patterns in products or reps.
Thanks for sharing a useful solution
Regards
George
You're welcome George. Nice work.
What if I want to find the max value for a customer id and not the last value
You want the MAXIFS function
before the last payment date and amount show in excel
It is great thanks! is it possible to find the value previous the last one? I would like to calculate the difference between the last 2 numbers, and i don't want to update the formula every week :)
Sure. Cell J1 has been used in the formula for the total number of matches. You could have another cell such as K1 with =J1-1 and this will be the second from last value.
Then do two VLOOKUPs, one from the video - one finding the K1 value. Job done!
Great
Thanks.
You might be able to do it by sorting at 2 levels: Customer ID small to large and then Order ID large to small. Give it a try and tell me if it works
hi, great video. can you retrieve the sum of the last 5?
VLOOKUP can't. But we could use 5 VLOOKUPs each to return the last 5. Then sum them.
Pls can we nest the same process to Index Match that replaces Vlookup?
Sure. If VLOOKUP can do it, so can INDEX MATCH
@@Computergaga Thank you so much.
hi there, how does this change if the sorting changes, I i still need the last / latest date?
The sorting would ruin it. A better approach is required. You could uase the FILTER function, Power Query or if you are returning a number SUMIFS is maybe easiest.
Hi, thanks for the video it's really useful. Quick question for you though, when creating the helper column it is hardcoded to C2&COUNTIF($C$2:C2,C2), is there a way to create this using table referencing?
It would be something like [@Customer ID]&COUNTIF($C$2:[@Customer ID],[@Customer ID]), but i don't know how to make an absolute reference to cell C2, the first row in the column, using "table referencing".
I like to try to remove all hardcoding if possible.
I need the same thing. Did you get any solutions?
Hats off bro i want same with thing with index match could you plz suggest me any video for that
I don't have a video on INDEX and MATCH to fetch the last item, although I do have a INDEX-MATCH video.
An INDEX and MATCH alternative for this video would be =INDEX(F:F,MATCH(I4&J1,D:D,0))
thank you soo much bro it's working give me one more favor, i have multiple criteria suppose if i have date as 2nd lookup value then what should be my formula is????
The easiest way is to create a new concatenated column of the 2 criteria i.e. name and date. Then use the INDEX and MATCH or VLOOKUP to look down that column for the concatenated lookup value.
I have a video here showing what I mean with VLOOKUP searching for firstname and lastname - th-cam.com/video/ZqWTsmk2Jk8/w-d-xo.html
HOW TO DO CONDITIONAL FORMATING
WHEN MET THE CONDITION IT SHOWS THE CHECK ICON.
Highlight the values that you want to test. Click Conditional Formatting and then New Rule. Change the Two Colour Scale option to Icon Sets and then select the check icon from the list provided. You then set the criteria.
This defaults to looking at values as percentages. You probably want to change the Type column to Number and then set the criteria.
every body needs reverse Vlookup , but ms havent given yet. please ms give us RVlookup :)
I need to find the last payment made on a purchase order in a list that runs the payments horizontally in Excel, there is only one line item per PO with the months running across the top of the worksheet. I figured out how many payments have been made and I believe I have to use the Hlookup vs the Vlookup but I'm not sure. Can anyone help!?
Sure you can do the same approach but with a HLOOKUP for the row.
how show if the result value on the last column if is not a number?
This technique works for text also. It does not rely on being numeric to count occurrences or to concatenate the number after the value and then look it up.
Thanks for your comment Robert.
thanks ..
Just sort descending on date.
Count function uses a lot of memory when you have ten thousends of rows
It doesn't use that much. But anyhow we are looking for a specific ID so for your results we would also need to filter for that and then when we are finished maybe reset the filter and the sort. And the formula provides an automated solution for reports and other outputs.
But yes there are numerous other ways we can get the answer for this task. Each with their pros and cons.
sorry sir, i'm not good in english, Why your formula not running well if we want to take data from another sheet, or folder, i hope you understand what i mean.
The same can be applied to data on another sheet. As for the folder you would need to import the data into a worksheet first, being using this formula.
@@Computergaga ok Sir Thanks a lot, well done, running well if in one work sheet (Subscribe and thumb up done)
you can count when you have 18 lines and put that count to vlook up. I have 18,000 lines..do I write another formula to count? Not a feasible formula..max function should work
Depends on the data as to whether max would work. Another option is XLOOKUP if you have Excel 365. That can look from bottom up.
animal. keep them coming