Index, match and max function with specific criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1718409%22%20slang%3D%22en-US%22%3EIndex%2C%20match%20and%20max%20function%20with%20specific%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1718409%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20following%20formula%20to%20index%20and%20match%20a%20row%20with%20the%20highest%20value.%3C%2FP%3E%3CP%3E%3DINDEX(A5%3AA35%2CMATCH(MAX(G5%3AG35)%2CG5%3AG35%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20add%20an%20IF%20statement%20with%20a%20specific%20criteria%20to%20this%20formula%20so%20it%20indexes%20the%20row%20with%20the%20highest%20value%20if%20the%20value%20in%20another%20column%20%3D%20a%20specific%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bhowell_1-1601263061498.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222488i27129941A1EF9C66%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22bhowell_1-1601263061498.png%22%20alt%3D%22bhowell_1-1601263061498.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20is%20a%20screenshot%20of%20what%20I%20am%20working%20with.%26nbsp%3B%20I%20would%20like%20Q16%20to%20use%20that%20formula%2C%20only%20if%20the%20cell%20in%20the%20same%20row%20of%20B5%3AB35%20%3D%20%22ATV%20%2F%20SxS%22.%26nbsp%3B%20In%20other%20words%2C%20I%20want%20Q16%20to%20give%20me%20the%20name%20in%20column%20A%20that%20corresponds%20to%20the%20highest%20value%20in%20column%20G%20IF%20the%20value%20in%20column%20B%20%3D%20%22ATV%20%2F%20SxS%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20that%20makes%20sense.%26nbsp%3B%20I%20appreciate%20any%20help%20I%20can%20get.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1718409%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1718502%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2C%20match%20and%20max%20function%20with%20specific%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1718502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811105%22%20target%3D%22_blank%22%3E%40bhowell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(A5%3AA35%2CMATCH(MAX(INDEX((B5%3AB35%3D%22ATV%20%2F%20SxS%22)*G5%3AG35%2C))%2CG5%3AG35%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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.

 

bhowell_1-1601263061498.png

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

@bhowell 

 

You may try something like this...

 

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

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