Vlookup with ifs to compare cells in different rows based on conditions?

[somanyqs]

I'm scratching my head…
What I'm trying to set up is some kind of multiple vlookup/if statement, but not sure where to begin.

I'm looking to be able to type different things into the following cells:
A1: type in quarter of the year (say, 1/2013)
A2: type in the room of the house (kitchen, garage, etc.)
A3: type in kind of work (paint, electrical, drywall, etc.)

Then what I would like to have is A4 display who worked on that room during that quarter.
So A4 will return: tom, bill, bob, or "combination" (if multiple people worked in the same room on the same action in the same quarter - couple be 2 guys who did, could be 3 or more), and then A5 will return the median price.

The data would be in D:H (could start anywhere, say D5:H100000), with each row showing:
Date, room, worker, action, cost
This list can be very long - so I can have 20 workers working in the same room in the same quarter, and each working can do multiple things in each room, or work on the same action multiple times.

So for example
1/2012 bob kitchen electrical 100
1/2012 bill garage paint 50
1/2012 bob kitchen paint 250
1/2012 bill kitchen paint 300
1/2012 tom garage paint 125
1/2012 bob kitchen paint 225
1/2013 tom garage paint 125
1/2013 tom kitchen paint 350
1/2012 tom kitchen drywall 25
1/2012 tom garage paint 75
1/2012 bob kitchen electrical 150

So
A1 = 1/2012
A2 = garage
A3 = paint
Then
A4= combination
A5 = median of 50 + 125 + 75

Or
A1 = 1/2012
A2 = kitchen
A3 = electrical
Then
A4 = bob
A5 = median of 100 + 150

I'm wondering for..
A4:
Do I need to somehow use a vlookup and if to check all the cases of 1/2012, garage and paint and somehow see if they return the same worker (tons of workers, however)?
A5:
I have learned how to calculate the median for this example, which I think would be
A5 = {MEDIAN(IF(D:D=A1,IF(F:F=A2,IF(G:G=A3,H:H))))}
Unless it is a combination, in which case I'm not sure what to do.

I feel like this should be one of those questions you can just break down, but I'm a bit stuck...

Stuck on a Tuesday..

 

[Aladin Akyurek]

Aladin and Akashwani...fabulous! Thank you!
I think from these I should be able to modify my work (and learned quite a bit here), much appreciated-

That's great to hear.