finding the LAST time something appears in a list?

**hunter484** · 04-17-2009, 06:48 PM

The stupid paperclip is worthless, a search here turned up zilch, and I can't find any tutorials that are remotely related to my question, so here goes:

I have a list of numbers in Column A (labeled "Number"), with a list of dates in Column B (labeled "Date & Time"). Every date in Column B is unique, but some numbers in Column A appear more than once.

I am trying to create a simple formula that will automatically search for the LAST time a unique number appears in Column A (ignoring all previous instances of this number), and copy the date right next to it in Column B to another location on another worksheet.

I tried fussing with several of the built-in functions, but I really don't know what I'm doing, and the built-in help documents are filled with too much mathematical/programmer technical terminology for me to understand. All the tutorials I've looked at seem to think I want to add some numbers together, but I don't want to do any calculations!

I just want a simple, easy-to-understand solution. Can somebody please help? This is so frustrating...

**Domenic** · 04-17-2009, 07:47 PM

Assuming that D2 contains the number of interest, try...

=LOOKUP(2,1/(A2:A100=D2),B2:B100)

Adjust the ranges, accordingly.

**windknife** · 04-21-2009, 12:59 AM

If your column B is not sorted, you can try

=MAX(IF(A2:A100=D2,B2:B100))

commited with Ctrl+Shift+Enter

**hunter484** · 04-23-2009, 12:39 AM

Column D is empty.
I only have a list of numbers (some of which appear more than once) in Column A, and unique dates in Column B. Nothing else.

Column B is sorted, from the oldest date to the most recent one.

To illustrate:

Column A ----- Column B
12345678 ----- January 1
38561034 ----- January 2
73857302 ----- January 3
12345678 ----- January 4

If my table looked like that, I want a formula that returns "January 4" when I ask it to look for "12345678", and ignore older times that this number appears.

I don't know if it will help, but since the list is sorted by Column B, maybe it could search from the bottom of the list and look up?

But it would have to be capable of automatically updating (like, if I were to add "12345678" and "February 5", it should display the new date.

That's what I'm trying to do.

**DonkeyOte** · 04-23-2009, 02:47 AM

Domenic has provided a solution, D2 is meant to represent the cell containing the ID of interest, eg: 12345678

windknife's array will also work if the dates in Column B are true date values (ie numeric - Domenic's will work regardless), and again D2 is meant to hold the ID of interest - this formula must be committed with CTRL + SHIFT + ENTER