Need result from Column A based on MAXIFS result from Column B

Hello.

I'm having trouble with writing a formula that will give the, for instance, corresponding value in Column A based on the result of a MAXIFS formula that finds the Max value in Column C, if it first matches a Value in Column B.

I've used the below code to find the Max Value in Column C, based on first matching a criteria found in Column B.

Please Login or Register  to view this content.

Yet, if I use OFFSET(MATCH) to try and retrieve the value I need from Column A, it gets tripped up with any duplicates. I've used the following:

Please Login or Register  to view this content.

I know the problem is that the code is finding the first instance of the matching value found from the MAXIFS formula, so what I need help with is forcing the formula to find the find the answer in Column C (based on matching a criteria in Column B) and then outputting the value in Column A based on finding what Row the answer to the previous MAXIFS was found in...

My apology for not being able to explain more concisely. I've attached a workbook where i've tried to dumb it down to its simplest parts.

Thanks in advance.

Use these normal formula

In E5
=MAX(INDEX(('Sheet 2'!$B$2:$B$202=E$4)*'Sheet 2'!$C$2:$C$202,0),0)
Copy across

In E10
=INDEX('Sheet 2'!$A$2:$A$202,MATCH(1,INDEX(('Sheet 2'!$B$2:$B$202=E4)*('Sheet 2'!$C$2:$C$202=E5),0),0))
Copy across

Well, your data in Col L of the 'office' sheet has text in it which is causing the value error. Instead of 0 you have --
Replace those with zero and use the formula
=MAX(INDEX((Office!$E$3:$E$3918=E$4)*Office!$L$3:$L$3918,0),0)
Notice the range starts at Row 3

The other option is to use this array formula. This will work even with text
=MAX(IF(Office!$E$3:$E$3918=E$4,Office!$L$3:$L$3918,0))
Confirm with Ctrl+Shift+Enter and not just Enter