 Highlighted

# 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 criteria to this formula so it indexes the row with the highest value if the value in another column = a specific text. This is a screenshot of what I am working with.  I would like Q16 to use that formula, only if the cell in the same row of B5:B35 = "ATV / SxS".  In other words, I want Q16 to give me the name in column A that corresponds to the highest value in column G IF the value in column B = "ATV / SxS".

Hopefully that makes sense.  I appreciate any help I can get.

2 Replies
Highlighted

# Re: Index, match and max function with specific criteria

You may try something like this...

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

# Re: Index, match and max function with specific criteria

@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.