help PLEASE Urgent

  • Thread starter Thread starter mathew.heys
  • Start date Start date
M

mathew.heys

I have the following case that I am having problems with

A B
1 Number Date
2 109956 1/08/1983
3 109956 9/08/1983
4 110190 30/10/1984
5 110190 5/11/1983
6 110190 2/11/1984

Now I would like a formula which would return the max date for a
certain number, For example, the result on
109956 would return 9/8/1983
110190 would return 2/11/1984
 
Suppose the number you are looking for is in C2, then put this array*
formula in D2:

=MAX(IF(A$2:A$6=$C$2,B$2:B$6,0))

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it
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 - you must not type these yourself.

Format D2 as date, and adjust the ranges to suit.

Hope this helps.

Pete
 
Suppose the number you are looking for is in C2, then put this array*
formula in D2:

=MAX(IF(A$2:A$6=$C$2,B$2:B$6,0))

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it
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 - you must not type these yourself.

Format D2 as date, and adjust the ranges to suit.

Hope this helps.

Pete

On Oct 29, 11:28 pm, (e-mail address removed) wrote:> I have the following case that I am having problems with
Click to expand...

Thanks Pete

but the value I am looking for is in colum B
I am looking for the Max date if for a number.

Hope this makes sence
 
I know that, but presumably you want the maximum date for that number
to be returned somewhere (I suggested in D2), and you want to be able
to vary the number you are looking for (I suggested C2).

If instead of those two cells you were to use X1 and Y1, then put your
number in X1 and this array formula in Y1:

=MAX(IF(A$2:A$6=$X$1,B$2:B$6,0))

If you've got more than 6 rows of data then change the 6. Don't forget
to use CSE to commit.

Hope this helps.

Pete
 
Back
Top