How to find max value with INDEX-MATCH and MAX

0
votes

I am newbie to excel. I have this following data in excel sheet.

desc

I want to have the value of G11 returned 27, as the value of its corresponding cell in column D, which is D11 matches to E8, and E9, which then correspond to the values of 14 and 27 in column J. Could someone tell me how to return a match value when I have two match situations and I need the maximum one. I am using the following formula.

=IF(D11=1,0,INDEX($H$4:$H$13,MATCH(D11,$E$4:$E$13,0),1))

I know if I use

=IF(D11=1,0,INDEX($H$4:$H$13,MATCH(D11,$E$4:$E$13,0)+1,1))

it returns me with the value 27 or greater of the both values but it will not work if I have three values and I want the maximum.

2
excelexcel-formula

2 Answers

1
votes

This will do it confirmed with ctrl+shift+enter:

{=MAX(IF(E4:E13=D11,J4:J13))}

Curly braces should not be entered manually, be sure to confirm with ctrl+shift+enter

This says:

  1. Look for rows where the value in E matches D11
  2. If you find one, return the value in column J
  3. Give the maximum value of the result you find

This will cause errors if there are no matches. You also probably are designing your sheet in a not ideal way.

1
votes

You can use the formula:

=IF(D11=1,0,MAX(IF(D11=$E$4:$E$13,$H$4:$H$13,0)))

entered as an array formula (i.e. you press Ctrl+Shift+Enter instead of Enter alone) for G11.

MATCH returns the first match, so that's why I'm using another IF instead.