(Msg. 1) Posted: Fri Oct 16, 2009 7:45 am
Post subject: lookup if? sumproduct if? index/match? Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
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?
(Msg. 2) Posted: Fri Oct 16, 2009 7:59 am
Post subject: RE: lookup if? sumproduct if? index/match? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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>}"
If this post helps click Yes
---------------
Jacob Skaria
"Brad Autry" wrote:
> 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
(Msg. 3) Posted: Fri Oct 16, 2009 8:02 am
Post subject: Re: lookup if? sumproduct if? index/match? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
On Oct 16, 3:45 pm, Brad Autry <BradAu... RemoveThis @discussions.microsoft.com>
wrote:
> 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
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office