Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:
Solution examples
I'm trying to figure out how to have a value found in column c placed into column d if the value in column a equals that found in column b. Does that make sense?
Solved by I. H. in 40 mins
I have an excel spreadsheet with two worksheets. And the following formula is not pulling the data. =VLOOKUP($A$2:$A$566,'Module Type Info'!$A$2:$D$97,4,FALSE) In sheet 1, I am using all data in column 1 for the lookup In sheet 2, I have selected the first 4 columns of data for array I want to pull data from sheet 2, column 4 into sheet 1 Both tabs are sorted alphabetically. Confirmed that the value in column 4 of sheet 2 is a TEXT field. Why is this not pulling the info from sheet 2 into sheet 1
Solved by F. L. in 39 mins
Good morning, I need a cell formatting solution to display leading zero in my excel table with the following conditions. 1. Cannot format as text 2. cell size is not fixed. Please let me know if there is a solution.Thanks in advance
Solved by V. C. in 11 mins
When i do a VLOOKUP, I cannot autofill the formula for the rest of the table. It copies the initial cell that the VLOOKUP was done.
Solved by G. A. in 20 mins
I need a formula (I think VLOOKUP) that will enable me to: Use data from one column, compare with another and tell me the difference. Im a teacher and want to work out the amount of marks needed to the next grade boundary. I've created the rest, but this has me stumped!
Solved by F. A. in 28 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc