CODEWITHSUNDEEP
excelexcel-formulapivot-tablevlookupexcel-2013
Huzz
Huzz

Reputation: 1

Lookup with multiple criteria, one a MAX value

I am trying to lookup the LOCATION of an employee (NAME) and their MANAGER from the most recent month (largest month number) in a particular QUARTER in data like this: 

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

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 and MATCH array formula: 

{=INDEX($B$2:$B$7,MATCH(A12,IF($D$2:$D$7=D12,$A$2:$A$7),0))}

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: 

{=INDEX($B$2:$B$7,MAX($E2:$E7,MATCH(A12,IF($D$2:$D$7=D12,$A$2:$A$7),0)))}

but that didn't quite get me there either.

What formula do I need to get this to work?

Upvotes: 0

Views: 415

Answers (2)

rwking
rwking

Reputation: 1032

I think a pivot table is probably the best option and can easily be modified with the filters when new entries are added to the underlying data. I was working on a solution with a formula, but it requires you to add a lookup column.

The formula for the lookup column is: =E6&" "&H6&" "&I6

enter image description here

I wasn't clear on how the OP was going to be "entering" his employee name and quarter, so I had to make an assumption that it would be in a separate column:

enter image description here

And the formula in column B (which is cumbersome) is: 

=VLOOKUP(A6&" "&MAX(IF(H1:H100=NUMBERVALUE(RIGHT(A6,1)),I1:I100)),$D$6:$G$11,3,FALSE)&", managed by "&VLOOKUP(A6&" "&MAX(IF(H1:H100=NUMBERVALUE(RIGHT(A6,1)),I1:I100)),$D$6:$G$11,4,FALSE)

But it works, and as long as the lookup range is adjusted, is scaleable.

Upvotes: 1

pnuts
pnuts

Reputation: 59485

I think I'd choose a PivotTable for its versatility and speed:

SO32404865 example

Upvotes: 1

Related Questions