Get last entry by month and year

Do you want to get the last entry by month and year in Excel? There are large data piled up in Excel sheets. If you want a single entry, suppose the last one according to month and year. What would you do? Here is a simple and easy guide for those who want efficient ways to get the last entry.

Formula

=LOOKUP(2,1/(TEXT(dates,"mmyy")=TEXT(A1,"mmyy")),values)

Procedure:

We are here using the LOOKUP function of Excel. It is the main secret of Excel. Why do you think large companies hire people with Excel knowledge? They are familiar with the functions and formulas of the Excel that will help organizations to get quicker results. Getting the last entry, we will hook up the LOOKUP and TEXT function.

Example:

Here is an easy example which will help you in understanding better. We are taking random data here which you can see in the given figure below.

Figure 1.Get last entry by month and year by using the LOOKUP function

The formula we used above is:

=LOOKUP(2,1/(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy")),$C$5:$C$13)

The important point here is that the lookup value here 2 is quite large.

Description of using the formula:

Let’s have a little numerical discussion of the formula to see how Excel provides the best solutions.

The inside expression in Excel can be written as:

(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy"))

The following formula gives a new array containing data we are using.

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Here True represents the dates of same month and year which you are looking for. This is why LOOKUP is best to get the last entry by month and year. It manages everyone time.

Importance of LOOKUP function:

The true importance of LOOKUP function is that it helps in looking for specific items or information in large data which one cannot study throughout. Excel resolves this query so everyone can have error-free and accurate results.