Forum Discussion

bhowell's avatar
bhowell
Copper Contributor
Sep 28, 2020

Index, match and max function with specific criteria

Hello,   I am using the following formula to index and match a row with the highest value. =INDEX(A5:A35,MATCH(MAX(G5:G35),G5:G35,0))   I would like to add an IF statement with a specific criter...
excel
Formulas and Functions
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Sep 28, 2020

bhowell ,,,

 

You may try this array (CSE) formula:

 

 

 

{=VLOOKUP(MAX(IF($B$5:$B$35="ATV/SxS", $G$5:$G$35,0)),$A$5:$G$35,1,FALSE)}

Or you may try this non-array formula even:

=INDEX(A5:A35,MATCH(SUMPRODUCT(MAX((B5:B35="ATV/SxS")*(G5:G35))),G5:G35,0))

 

 

 

  • Finish array (CSE) formula with Ctrl+Shift+Enter.
  • Adjust cell references in the formula as needed. 

Resources