Lookup function to return highest value

[BDAvs]

On Sheet1, I have a number of categories all with running totals. O
Sheet2 I have some of the same categories. I want to continue th
running total on Sheet2, but need to know how to return the runnin
total (largest value in each category) from Sheet1.

e.g.

Sheet 1
Column N Column AL
Apples 1
Apples 4
Apples 6
Oranges 2
Oranges 4
Oranges 9
...

Sheet 2
Column N Column AL (want)
Apples 6
Apples
Apples
Apples
Oranges 9
Oranges
Oranges

Thanks for any help!


B

 

[Claus Busch]

Hi,

Am Thu, 6 Sep 2012 14:40:57 +0000 schrieb BDAvs:

Sheet 1
Column N Column AL
Apples 1
Apples 4
Apples 6
Oranges 2
Oranges 4
Oranges 9
..

Sheet 2
Column N Column AL (want)
Apples 6
Apples
Apples
Apples
Oranges 9
Oranges
Oranges

in Sheet2 AL2:
=MAX((Sheet1!$N$2:$N$100=N2)*Sheet1!$AL$2:$AL$100)
or
=MAX(IF(Sheet1!$N$2:$N$100=N2,Sheet1!$AL$2:$AL$100))


Regards
Claus Busch

 

[BDAvs]

Have a look at the attached. Is this what you mean?

It's an "array formula" so you need to confirm them by pressing Ctrl
Shft and Enter rather than just Enter.

That was helpful. Thanks for taking the time to look into this!!

B

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

That was helpful. Thanks for taking the time to look into this!!

BD

I presume that's a yes then? Problem solved? :

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Paga Mike]

In N1 in Sheet2 enter:
Apples
In Sheet2 AL1 enter:

=MAX(IF(Sheet1!N1:N15=N1,Sheet1!AL1:AL15))

This is an array formula that must be entered with CNTRL-SHFT-ENTE
rather than just the ENTER key