Sep 27 2020 08:23 PM
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.
Sep 27 2020 09:44 PM
You may try something like this...
=INDEX(A5:A35,MATCH(MAX(INDEX((B5:B35="ATV / SxS")*G5:G35,)),G5:G35,0))
Sep 28 2020 12:44 AM - edited Sep 28 2020 01:08 AM
@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))