Criteria to return maximum value where x = x
 

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

Criteria to return maximum value where x = x
 

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.

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
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

Criteria to return maximum value where x = x
 

Biff,

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

Regards, Robert
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
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

Criteria to return maximum value where x = x
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Biff,

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

Regards, Robert
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
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