MAX Value with Multiple Criteria

[robocop1906]

Trying to get the formula below to work.
It works with out the ('Data Sheet'!Y$4:Y$1000="Complete") when I try to add a second criteria.

Tyring to retrurn the greatest value based on multiple criteria

=MAX(('Data Sheet'!D$4:D$1000=A46),('Data Sheet'!Y$4:Y$1000="Complete"))*'Data Sheet'!U$4:U$1000

 

[Domenic]

Try...

=MAX(IF('Data Sheet'!D$4:D$1000=A46,IF('Data Sheet'!Y$4:Y$1000="Complete",'Data Sheet'!U$4:U$1000)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

 

[robocop1906]

That worked, is there a way to index the Max value and return what's in column A? So same logic, I just want to return what's in column A:A vs the actual value in U:U

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX('Data Sheet'!A$4:A$1000,MATCH(A2,IF('Data Sheet'!D$4:D$1000=A46,IF('Data Sheet'!Y$4:Y$1000="Complete",'Data Sheet'!U$4:U$1000)),0))

...where A2 contains the result of the first formula.

 

[gecs]

For the max value you may also try:

=LOOKUP(2,1/(('Data Sheet'!D$4:D$1000=A46)*('Data Sheet'!Y$4:Y$1000="Complete")*('Data Sheet'!U$4:U$1000=MAX(('Data Sheet'!D$4:D$1000=A46)*('Data Sheet'!Y$4:Y$1000="Complete")*('Data Sheet'!U$4:U$1000)))),'Data Sheet'!U$4:U$1000)

confirmed only with Enter (NOT AN ARRAY FORMULA!)

Aladin's magic LOOKUP(2,1/...) formula is so versatile, I can't stop trying to use it instead of an array formula

 

[Aladin Akyurek]

For the max value you may also try:

=LOOKUP(2,1/(('Data Sheet'!D$4:D$1000=A46)*('Data Sheet'!Y$4:Y$1000="Complete")*('Data Sheet'!U$4:U$1000=MAX(('Data Sheet'!D$4:D$1000=A46)*('Data Sheet'!Y$4:Y$1000="Complete")*('Data Sheet'!U$4:U$1000)))),'Data Sheet'!U$4:U$1000)

confirmed only with Enter (NOT AN ARRAY FORMULA!)

Aladin's magic LOOKUP(2,1/...) formula is so versatile, I can't stop trying to use it instead of an array formula

It's also an array-grinding formula, although control+shift+enter signalling is not required... And, it's here probably not efficient either.