Lookup with multiple criteria and MAX field value

[huzzer]

I am trying to lookup the Location of an employee and their Manager from the most recent month (largest month number) in a particular Quarter. So, if I know the name of the employee and the quarter I'm looking up, the results should display who their last manager was and the location they were in, as these may change month to month.
I have used an INDEX & MATCH array formula:
<code>{=INDEX($B$2:$B$7,MATCH(A12,IF($D$2:$D$7=D12,$A$2:$A$7),0))}</code>
but this just provides the first match and not necessarily the most recent month in that quarter. I attempted to include a MAX function which looked something like this:
<code>{=INDEX($B$2:$B$7,MAX($E2:$E7,MATCH(A12,IF($D$2:$D$7=D12,$A$2:$A$7),0)))}</code>
but that didn't quite get me there either.
What formula do I need to get this to work?

Below is the Monthly Data

Name	Location	Manager	Quarter	Month
Ryan Smith	Sioux Falls	Rick James	3	7
Jane Doe	Tampa	Bobby Brown	3	7
John Rogers	Tampa	Tracy Lane	3	7
Ryan Smith	Sioux Falls	Rick James	3	8
Jane Doe	Denver	Thomas Craig	3	8
John Rogers	Tampa	Cody Davis	3	8

<tbody>
</tbody>

Below is the Quarterly Data (Location and Manager fields are blank),
I have the answers completed as to what the results should be. FYI: The month is not necessarily in Ascending or Descending order, hence to needing to find the highest value of month.

Name	Location	Manager	Quarter
Ryan Smith	ans.(Sioux Falls)	ans.(Rick James)	3
Jane Doe	ans.(Tampa)	ans.(Bobby Brown)	3
John Rogers	ans.(Tampa)	ans.(Tracy Lane)	3

<tbody>
</tbody>

 

[huzzer]

I am trying to lookup the Location of an employee and their Manager from the most recent month (largest month number) in a particular Quarter. So, if I know the name of the employee and the quarter I'm looking up, the results should display who their last manager was and the location they were in, as these may change month to month.
I have used an INDEX & MATCH array formula:
<code>{=INDEX($B$2:$B$7,MATCH(A12,IF($D$2:$D$7=D12,$A$2:$A$7),0))}</code>
but this just provides the first match and not necessarily the most recent month in that quarter. I attempted to include a MAX function which looked something like this:
<code>{=INDEX($B$2:$B$7,MAX($E2:$E7,MATCH(A12,IF($D$2:$D$7=D12,$A$2:$A$7),0)))}</code>
but that didn't quite get me there either.
What formula do I need to get this to work?

Below is the Monthly Data

Name	Location	Manager	Quarter	Month
Ryan Smith	Sioux Falls	Rick James	3	7
Jane Doe	Tampa	Bobby Brown	3	7
John Rogers	Tampa	Tracy Lane	3	7
Ryan Smith	Sioux Falls	Rick James	3	8
Jane Doe	Denver	Thomas Craig	3	8
John Rogers	Tampa	Cody Davis	3	8

<tbody>
</tbody>

Below is the Quarterly Data (Location and Manager fields are blank),
I have the answers completed as to what the results should be. FYI: The month is not necessarily in Ascending or Descending order, hence to needing to find the highest value of month.

Name	Location	Manager	Quarter
Ryan Smith	ans.(Sioux Falls)	ans.(Rick James)	3
Jane Doe	ans.(Denver)	ans.(Thomas Craig)	3
John Rogers	ans.(Tampa)	ans.(Cody Davis)	3

<tbody>
</tbody>

I updated answers, they were wrong in initial post.

 

[oldbrewer]

Below is the Monthly Data					max month
					8
Name		Location	Manager	Quarter	Month
Ryan Smith	Ryan Smith7	Sioux Falls	Rick James	3	7
Jane Doe	Jane Doe7	Tampa	Bobby Brown	3	7
John Rogers	John Rogers7	Tampa	Tracy Lane	3	7
Ryan Smith	Ryan Smith8	Sioux Falls	Rick James	3	8
Jane Doe	Jane Doe8	Denver	Thomas Craig	3	8
John Rogers	John Rogers8	Tampa	Cody Davis	3	8
Below is the Quarterly Data (Location and Manager fields are blank),
I have the answers completed as to what the results should be. FYI: The month is not necessarily in Ascending or Descending order, hence to needing to find the highest value of month.
Name		Location	Manager	Quarter
Ryan Smith		ans.(Sioux Falls)	ans.(Rick James)	3
Jane Doe		ans.(Tampa)	ans.(Bobby Brown)	3
John Rogers		ans.(Tampa)	ans.(Tracy Lane)	3
Ryan Smith	Ryan Smith8	Sioux Falls	Rick James	3
		formula giving sioux falls
if you do not like the helper column you can copy the output to a new location	=OFFSET($B$12,MATCH($B31,$B$13:$B$18,0),1)
		the 1 at the end is changed to 2 and 3 for next 2 columns

<colgroup><col span="2"><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[huzzer]

I did not like the idea of using another column or key, or copying the output to a new location. There is a lot more fields to this than what i provided that has all of the actual monthly and quarterly metrics that end up in a pivot table. But, the idea of using a MAX Month key got me thinking. So in a cell far away from all of my data (AA1) i had it return the MAX month of my monthly worksheet. This allowed me to just add an additional MATCH to my array formula and i get the results.

Formula now looks like this:
{=INDEX('Monthly Data'!$B:$B(Site),MATCH('Quarterly Data'!$A2(Team Name)&'Quarterly Data'!$B2(Quarter)&'Quarterly Data'!$AA$1(MAX Month),'Monthly Data'!$A:$A(Team Name)&'Monthly Data'!$D:$D(Quarter)&'Monthly Data'!$E:$E(Month),0))}

 

[huzzer]

Actually, that won't work either. That does work if every employee remained with the company every month of the quarter. However, if an employee only has metrics for Month 7 and then not employed in Month 8, i still need the results to display their site and manager in month 7

 

[willhouston]

If the results do truly change month only month to month (that is, no person would have two records in quarter 3, month 7), you could use that fact to do cascading index-match lookups, the structure of which would be something like:

IF (record is available for <name> in month <(quarter - 1)*3 + 3>) THEN return Location
ELSE IF (record is available for <name> in month <(quarter*3 - 1) + 2>) THEN return Location
ELSE IF (record is available for <name> in month <(quarter - 1)*3 + 1>) THEN return Location

This works by using the fact that there are only 3 months per quarter, and you want the most recent.

It's a beast of a formula. Here's something I threw together without regard to which rows/columns I was in:

=IF(ISNA(INDEX($A$1:$E$7,MATCH(A13&(D13-1)*3+3,$A$1:$A$7&$E$1:$E$7,0),2))=FALSE,
               INDEX($A$1:$E$7,MATCH(A13&(D13-1)*3+3,$A$1:$A$7&$E$1:$E$7,0),2),
               IF(ISNA(INDEX($A$1:$E$7,MATCH(A13&(D13-1)*3+2,$A$1:$A$7&$E$1:$E$7,0),2))=FALSE,
                           INDEX($A$1:$E$7,MATCH(A13&(D13-1)*3+2,$A$1:$A$7&$E$1:$E$7,0),2),
                           IF(ISNA(INDEX($A$1:$E$7,MATCH(A13&(D13-1)*3+1,$A$1:$A$7&$E$1:$E$7,0),2))=FALSE,
                                       INDEX($A$1:$E$7,MATCH(A13&(D13-1)*3+1,$A$1:$A$7&$E$1:$E$7,0),2),
                                       "Not found")))

I'm not excellent at getting those lines to line up, but you will notice the first index formula seen matches the second, the third matches the fourth, and the fifth matches the sixth. The "ISNA" function tells you whether a valid result was returned, so if that is false, rerun the function to get the value.

You could write that pretty easily as a function in VBA using a structure very similar to what I outlined before.

I actually encountered a similar problem in a workbook I was working on a few months ago. My solution was to move it all to a database (Access works), where you could run a very, very, very simple query to pull it out, something like: SELECT Name, Location, Manager, Quarter, MAX(Month) As Month FROM TABLE GROUP BY Name, Location, Manager, Quarter;</name></name></name>