Go Back

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. Here is a simple and easy guide for those who want efficient ways to get the last entry.

How do we get last entry by month and year?

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

Procedure

We are here using the LOOKUP function 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 use 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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have a problem regarding to Get last entry by month and year
Solved by X. L. in 29 mins
trying to get current (today, last 7 days, month, year, all time) Net Profit sum of that day or last 7 days or month or year. So A4:a999 will be my dates, K4:k999 will be profits,
Solved by B. B. in 21 mins
I need to create a report that shows the last 2 years and current YTD sales by state, by month, by year in a side to side comparison as well as find the average by month. I have no clue where to start. Tab - Sheet 5.
Solved by A. L. in 14 mins

Leave a Comment

avatar