John Smith

asked on

# Maximum / Lookup Formula Question

Hi All,

I have been struggling with this one!

I need a formula which looks up the maximum value in column A for each unique number in column B and puts the text "end" in column C. please see working where I have manually entered "end" where I want it. All other cells in column C should be blank.

Thanks!

I have been struggling with this one!

I need a formula which looks up the maximum value in column A for each unique number in column B and puts the text "end" in column C. please see working where I have manually entered "end" where I want it. All other cells in column C should be blank.

Thanks!

Did you manage to upload something?

ASKER

Sorry!

Experts-Exchange.xlsx

Experts-Exchange.xlsx

Put this formula in C3 and copy down

=IF(B3<>B4,"end","")

=IF(B3<>B4,"end","")

ASKER

That does work, however I need something a bit more robust as the data might not be in order.

Just show me a sample please.

ASKER

Attached has data not in order.

Experts-Exchange-v2.xlsx

Experts-Exchange-v2.xlsx

You did not show the desired results.

ASKER

Sorry! try again!

Experts-Exchange-v3.xlsx

Experts-Exchange-v3.xlsx

This is an ARRAY formula and must be entered using ctrl-shift-enter

=IF(B3=MAX(IF($C$3:$C$10000=C3,$B$3:$B$10000)),"end","")

=IF(B3=MAX(IF($C$3:$C$1000

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.