Lookup function to return highest value
 

On Sheet1, I have a number of categories all with running totals. On Sheet2 I have some of the same categories. I want to continue the running total on Sheet2, but need to know how to return the running 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!


BD

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.

Lookup function to return highest value
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

BD

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