# MAX Value with Multiple Criteria

#### robocop1906

##### Board Regular
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

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Domenic

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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

##### Active Member
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 ##### MrExcel MVP
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.