Criteria to return maximum value where x = x

[Rob]

Good morning,

I have an Excel file with two sheets, sheet 1 contains two columns of data,
column A has text and column B has numbers. In sheet 2, I have a list if
unique text that matches that in column A of sheet 1. I need to place a
formula alongside the unique list in sheet 2 to return the highest
corresponding value in sheet 1 i.e. the value in column B.

Below is an example of what I'm looking to achieve. Any pointers most
welcome.

Thanks, Rob

col a col b
01 30
02 29
01 31
02 30
01 34

col c col d
01 want to return 34
02 want to return 30

 

[T. Valko]

Leading 0's are almost always a pain!

Try this array formula** :

=MAX(IF(Sheet1!A$1:A$5=C1,Sheet1!B$1:B$5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down as needed.

 

[Rob]

Biff,

Just the job, worked as required. Saved me hours!

Regards, Robert

 

[T. Valko]

You're welcome. Thanks for the feedback!