Excel MATCH and MAX and return value of specific column

[mtk1985]

Hi Guys,

Have one that I could not find documented anywhere. I did search as much as I can before posting but cant quite get the right formula for this task.

I am using this to perform half the task at hand
1. Look in column A for all cells that contain the word "EMEA"
2. For these cells only containing "EMEA" give me the MAX value of cell in column C
=MAX(IF(A:A="EMEA",C:C))

The above formula gives me the actual MAX value but what I actually what is for 1 and 2 to be logical statements (IF) and the final output should be the value in adjacent cell B

Column A = Region (APAC, EMEA, US, JPN) etc.
Column B = Agent Name
Column C = Number of contacts

So basically, performing the following:
1. Check column A for EMEA only
2. Check column C to find the largest number of contacts
3. Check column B return the name of the Agent.

My worksheet is actually using column's from A to AJ and each column has different data but the above are within the A:AJ range but for the purpose of asking my question I am just sticking with A:C

If I can get a formula that doesn't require (CTRL+SHIFT+ENTER) that would be amazing but I will use it if that's all we have.

Hope my question makes sense

Appreciate your support.

 

[JackDanIce]

Welcome to the board. Unfortunately, this is an array formula (i.e. does require CTRL+SHIFT+ENTER), try:

=INDEX(B:B,MATCH(MAX(IF(A:A="EMEA",C:C)),C:C,0))

Note, if you have two equal max values where A:A = EMEA, then the above will return the agent name of the first max value it finds in col. C. Hopefully someone else can suggest something closer to what you require.

 

[XOR LX]

=INDEX(B:B,MATCH(MAX(IF(A:A="EMEA",C:C)),C:C,0))

Note, if you have two equal max values where A:A = EMEA, then the above will return the agent name of the first max value it finds in col. C.

Actually, this formula could equally return an agent name for which the corresponding value in A:A is not "EMEA". In order to avoid this, use:

=INDEX(B:B,MATCH(MAX(IF(A:A="EMEA",C:C)),IF(A:A="EMEA",C:C),0))

though I also strongly recommend not using entire column references in array formulas.

Regards

 

[JackDanIce]

Agree about not using the entire column reference, but I've just tried with some dummy data and it does only return a value from column B, where A is and only = "EMEA"

(Max value for EMEA is 9, max value for non-EMEA is 10, MAX(IF($A$1:$A$10="EMEA",$C$1:$C$10)) and agent value returned has a col C value of 9 and and a col A value EMEA. Alternatively, replacing every "EMEA" with "J" as an arbitrary value and the formula returns #N/A as no match for "EMEA")

 

[XOR LX]

Agree about not using the entire column reference, but I've just tried with some dummy data and it does only return a value from column B, where A is and only = "EMEA"

(Max value for EMEA is 9, max value for non-EMEA is 10, MAX(IF($A$1:$A$10="EMEA",$C$1:$C$10)) and agent value returned has a col C value of 9 and and a col A value EMEA. Alternatively, replacing every "EMEA" with "J" as an arbitrary value and the formula returns #N/A as no match for "EMEA")

Yes, but what if the max value in column C for cases where column A contains "EMEA" also happens to occur (again) in column C for some row where column A does not contain "EMEA", and whose row reference is smaller than that for the "EMEA" max?

What do you get if you then pass it to the INDEX/MATCH formula which you originally posted?

Regards

 

[mtk1985]

Thank you both. Both work to spec. Really appreciate it!

 

[XOR LX]

Thank you both. Both work to spec. Really appreciate it!

Clearly I would disagree.

I only ask that you test both solutions by manually working out what the max value is for EMEA-only rows, then artificially applying this value to a non-EMEA row somewhere earlier in the table and seeing what results you get.

I haven't seen your data, and although it may not be likely that there will be one or more non-EMEA lines which happen to have precisely the same value as the maximum for EMEA lines, I would imagine it is nevertheless possible.

Regards

 

[JackDanIce]

Good point XOR and correct, I didn't consider an equal or higher max value for non-EMEA that has a lower (earlier) row number.

 

[mtk1985]

Clearly I would disagree.

I only ask that you test both solutions by manually working out what the max value is for EMEA-only rows, then artificially applying this value to a non-EMEA row somewhere earlier in the table and seeing what results you get.

I haven't seen your data, and although it may not be likely that there will be one or more non-EMEA lines which happen to have precisely the same value as the maximum for EMEA lines, I would imagine it is nevertheless possible.

Regards

I couldnt see a difference of face value but I do see your point and it showed when I tested that particular scenario. I will move ahead with your solution. Much thanks for your solution and education