MAX Value Formula

[RetroMe]

Hi All,

I have the following formula that finds the maximum value in my table with a few criterias thrown in:

{=MAX((DATA!C2:C20000=D10)*(DATA!K2:K20000="OnRisk")*(DATA!H2:H20000>=E13)*(DATA!H2:H20000<=I13)*(DATA!N2:N20000="New Business")*(DATA!J2:J20000))}

But what I need also is to display the client name that is in column B.

So column 'J' has the maximum value of £1000 and column 'B' has the clients name, how do I display this?

Hope I have explained myself okay!

Thanks in advance.

Alex

 

[barry houdini]

Try

=INDEX(DATA!B2:B20000,MATCH(1,(DATA!J2:J20000=MAX(IF((DATA!C2:C20000=D10)*(DATA!K2:K20000="OnRisk")*(DATA!H2:H20000>=E13)*(DATA!H2:H20000<=I13)*(DATA!N2:N20000="New Business"),DATA!J2:J20000)))*(DATA!C2:C20000=D10)*(DATA!K2:K20000="OnRisk")*(DATA!H2:H20000>=E13)*(DATA!H2:H20000<=I13)*(DATA!N2:N20000="New Business"),0))

confirmed with CTRL+SHIFT+ENTER

 

[RetroMe]

Barry, once again you save the day.

That's two days in a row now!

Thank you so much.