lookup if? sumproduct if? index/match?

[Brad Autry]

Hi all:

I've a fairly lengthy set of data, but I'm primarily concerned with 3
columns. For my example, let's say that

ID number is in column A, Date of action is column B, Action type is column C.

On another sheet I would like to look up whatever action was the most recent
for the respective ID number. I'm not certain how to do this without doing
some sort of sort then remove duplicate to create another list that would
only have the most recent action. This seems a shoddy way of going about it.

Is there a way to insert a MAX function in to the date criteria for a lookup
or sumproduct function or index?

Many thanks in advance.

Brad

 

[Jacob Skaria]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=INDEX(Sheet1!$C$2:$C$100,MATCH(1,(Sheet1!$A$2:$A$100=A1)*
(Sheet1!$B$2:$B$100=SUMPRODUCT(MAX((Sheet1!A2:A100=A1)*
Sheet1!B2:B100))),0))

If this post helps click Yes

 

[Pete_UK]

Assume that in Sheet2 you enter an ID number in A1, then you can put
this array* formula in B1:

=MAX(IF(Sheet1!A1:A5000=A1,Sheet1!B1:B5000))

and this will return the most recent date for that ID number. Format
the cell as a date, otherwise it will show as a number like 39923.
I've assumed you have 5000 rows of data in Sheet1 - adjust as
necessary to cover your actual data.

* An array formula must be committed using the key combination of Ctrl-
Shift-Enter (CSE) rather than the usual <enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
subsequently amend the formula you will need to use CSE again.

Is this what you wanted? I'm not sure what the 3rd column is about.

Hope this helps.

Pete