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

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-ENTER rather than just the ENTER key.