VLOOKUP to Find the Last Value in a List

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

The VLOOKUP function is then used on the column of unique values.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[OHatef]

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

[mafiosol_buenavida]

This is brilliant and a useful yet logical alternative to the lookup method.

[wilfredocabuenas]

I applied this formula to find the last payment date of the customer in a big data, it works. Thank you!

[nguyenthanhmy]

Learn from your vids, applied to my job. Thank you so much

[itwturner]

I have been trying to figure this out for years. Thanks!

[wayneedmondson]

Hi Alan.. excellent.. had not thought of your method to solve this problem. So simple, yet so clever. Outstanding! Thumbs up!

[mp_petrov]

Thanks for your help, it makes sense to create a unique identifier and work with that.

[vickymadhas]

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

[wahabrajiwate]

Nice trick, can you avoid helper column and show the same, may be with index and match pls.

[davidtostenson]

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?

[davidreynolds]

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)

[georgemaragos]

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

[knzamy]

this is what i'm looking for..thanks mate!

[busta]

hi, great video. can you retrieve the sum of the last 5?

[julylahera]

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 :)

[itsvatsa]

hi there, how does this change if the sorting changes, I i still need the last / latest date?

[ExcelExpert]

Hats off bro i want same with thing with index match could you plz suggest me any video for that

[Toxic_-_James]

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?

[truyencaothe]

Nice work, very smart way. Thank a lot mate!

[Bashir_Kano]

Pls can we nest the same process to Index Match that replaces Vlookup?