Formula in Excel to display the date for the last occurrence of a value in a table

Hello,

In a table with chronologically sorted values, what Excel formula will display the date corresponding to the last time a given value appears in the table?

For example, suppose you have a spreadsheet with values in the range A1:B12 as in the following screenshot (Fig. 1):

Fig. 1
Also, suppose a user-entry box is placed at cell B15 (Fig. 2).

What formula in cell C15 will return the date from the table corresponding to the last occurrence of the user entry?

Fig. 2
Note that in the above example, the user entry "EAT" occurs in the table only once (06/06/14). However, suppose the user value occurs more than once in the table, as in the following screenshot (Fig. 3).

Fig. 3
Here, the entry "DEW" occurs twice (01/25/14 & 07/11/14) but according to the initial question, the formula in C15  should always display the most recent date, in this case 07/11/14.

What formula would accomplish that?

Thanks
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
See if this gives you what you want:

=LOOKUP(2,1/(B1:B12=B15),A1:A12)

Flyster
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
Hi

as an array formula
Enter with Ctrl-Shift-Enter

=MAX((B1:B12=B15)*A1:A12)

Regards
0
Rob HensonFinance AnalystCommented:
Assuming your real file will also have headers, you can use the DMAX function.

Above your data range put headers Date & Entry (or something more relevant).

Above your User Entry and Date formula put the same headers, B15 & C15 in the example when an extra row has been inserted to accommodate the headers and then use this formula:

=DMAX($A$1:$B$13,$C$15,$B$15:$B$16)

Thanks
Rob H
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

tomfarrarCommented:
Could you just sort the date column descending and use a simple vlookup?  Wouldn't that pick up the last date for the user?
0
WeThotUWasAToadAuthor Commented:
What a great thread — and what a great testimonial to the value of EE! I just got three different effective & accurate solutions, each using a different Excel function. This forum is the best resource I can imagine. Thanks a bunch for the responses!

Flyster, your solution:

        =LOOKUP(2,1/(B1:B12=B15),A1:A12)

is the easiest to use but the most difficult for me to understand.

I have seen (and I now use) a number of solutions — usually from Barry Houdini — which include a reciprocal argument and an equation as a single term. However, I don't understand their function or how they work.

As a result, I will shortly be opening a couple of new threads to see if I can obtain explanations. The titles are as follows:

• Range of cells equal to a single value in Excel functions
• Using an equation as a term in Excel functions
• Reciprocal arguments in Excel functions

Thanks
0
FlysterCommented:
Although rarely used, Lookup does have it's advantages over vlookup or hlookup. One of the requirements to use lookup is to have your data in ascending order, which you have. By dividing your array by 1 ("1/(B1:B12=B15)"), you end up with a result like this: {#DIV/0!;1,#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;} If it doesn't find the value (2) or any greater value in the range, it will return the last numerical value. Hope this makes sense!
0
WeThotUWasAToadAuthor Commented:
0
WeThotUWasAToadAuthor Commented:
tomfarrar
Could you just sort the date column descending and use a simple vlookup?  Wouldn't that pick up the last date for the user?
Tom, thanks for the response.

Yes. Your solution would work perfectly well for the example I included. However, when an example such as mine is used in a question, I think it is implied* that the best solution is one that functions correctly:

• for a list (database) of any size and,
• for a list that may be dynamic (ie getting additional entries or deletions of entries), and
• with as little manipulation as possible.

Therefore, although your solution would display the correct solution for a list of any size, the list would continually require resorting (manipulation) any time anything changes — which in many databases you may not be aware of.

*Someone please correct me if that statement is wrong.
0
tomfarrarCommented:
Sure.  Makes sense.  Whether sorting ascending or descending it requires additional steps.
0
WeThotUWasAToadAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.