VLookup for latest record

Dear Friends,

You must have noticed that the incredible VLOOKUP function of MS Excel always get you the result which occurs first in the given range if your range isn't consists of unique values. In that case, you either want to sort the data in descending order which isn't quite useful as several data structure can not afford to be sorted every time you use vlookup or you have to remove the earlier duplicated records in order to get the latest one with VLOOKUP.

Today, I am going to show you how to get the latest record through VLOOKUP by using it with another great function that is COUNTIF. That is how, you neither have to sort the data nor removal of earlier duplication would be required.



Suppose, you are maintaining a worksheet with 4 columns. Lets name it DataSheet.

You are using DataSheet to record the daily closing account balance of all the employees in order to link it with another worksheet named CurrentBalance which deals with other activities of them with their accounts. So, that is obvious that the CurrentBalance would require the latest account balance with the help of VLOOKUP to work further with current actual balances. But whats that? It is giving you the value which is available right adjacent to the lookup value occurring first in the DataSheet.
For example:
If you put simple VLOOKUP on CurrentBalance to get latest the A/C balance of MR.D, it would be giving you the value $1,041.00 which is not latest and not $1,593.00 which is the latest one that we need.

Here I am assuming here that you are well aware about what COUNTIF is and how does it work.

Now the trick is we will add a column on left most side on our DataSheet and put here the formula which will append the occurrence with the IDs and we can hide that column as well.

=C2&"-"&COUNTIF(C2:C22,C2)

Now look at the column A, It is showing us the same ID along with the hardcoded hyphen and the count of their occurrence and all the values with "-1" are the latest one.













So, Now we will do a little amendment in vlookup function at Column C on our CurrentBalance sheet.

=VLOOKUP(A2&"-1",[DataSheet.xls]Sheet1!$A$2:$E$22,5,0)


You see we have manually added -1 with the lookup value so that it would fetch the record available on the DataSheet with -1 along with the referencing ID.

That is it. We are done and we are getting the latest account balances without data sorting and duplication removal.
Please notice that Mr.D's current Account balance is now showing up as $1,593.00 :)


Cheers