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

somanyqs

Board Regular
Joined
Oct 2, 2007
Messages
76
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..
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

Would something like this help?...

Excel Workbook
ABCDEFGHIJ
1YearRoomWorkDateWorkerRoomActionCost
22012garagePaint01/01/2012bobkitchenelectrical100
301/01/2012billgaragepaint50
4CountMedian01/01/2012bobkitchenpaint250
537501/01/2012billkitchenpaint300
601/01/2012tomgaragepaint125
701/01/2012bobkitchenpaint225
8WorkerCost01/01/2013tomgaragepaint125
9bill5001/01/2013tomkitchenpaint350
10tom12501/01/2012tomkitchendrywall25
11tom7501/01/2012tomgaragepaint75
1201/01/2012bobkitchenelectrical150
13
Sheet15


The formulas in A9:B9 need entering with ctrl shift enter NOT just enter, they can then be copied down.

You may find some useful tips here....

Lookup multiple values in different columns and return a single value | Get Digital Help - Microsoft Excel resource

Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel | Get Digital Help - Microsoft Excel resource

Mr Excel & excelisfun Trick 81: Extract Multiple Records Based on Two Criteria VBA or Formula - YouTube

I hope this helps.

Ak
 
Upvote 0
Thanks much, still digesting.
The one piece I can't tell from the formulas (yet?), is how would I be able to tell whether all of the workers returned in A9:A1000 (lets say I copy those down - and it could be filled with lots of workers and their $ so I'm pretending this list can go to 1000+) were the same individual or not.

Like in the example above, maybe B4 header = "Who" and then B5 would be "bill" if all cells from A9:A1000 were "bill", or in this example, return "combined" since there is both bill and tom did work in 2012 painting the garage. Does that make sense? Hopefully I am explaining that OK-


Thanks for inputting the table. I'm on a Mac and all the tools I have seen to bring in tables seem to be for Windows machines. If there is another for Macs that I am missing however, please let me know. Thanks much for adding that table!
 
Upvote 0
Hi,

I'm not sure that I understand your question, sorry.

Is this what you want?...

Excel Workbook
ABCDEFGHIJ
1YearRoomWorkDateWorkerRoomActionCost
22012garagePaint01/01/2012bobkitchenelectrical100
301/01/2012billgaragepaint50
4CountWhoMedian01/01/2012bobkitchenpaint250
51Bill5001/01/2012billkitchenpaint300
601/01/2012tomgaragepaint125
701/01/2012bobkitchenpaint225
8WorkerCost01/01/2013tomgaragepaint125
9bill5001/01/2013tomkitchenpaint350
1001/01/2012tomkitchendrywall25
1101/01/2012tomgaragepaint75
1201/01/2012bobkitchenelectrical150
13
Sheet15


You can post sample data using one of the links in my signature, not sure which will work with a M*c though.

Ak
 
Upvote 0
Sorry if my reply was a little confusing.

Basically I was wondering in the example above if I input 2012, garage, Paint in A2, B2 and C2, that cell B5 would return the word "combination" (meaning that a combination of workers, both bill and tom, fill the criteria: painting the garage in 2012).
B5 could return the text "more than 1" or whatever other text we like, I was just trying to give an example of text being returned based on the fact that more than 1 worked was found to match the criteria in A2, B2 and C2.

So the formula in B5 is trying to figure out if just one worker meets the criteria (if so, it returns that worker's name), or if more than one worker meets that criteria (if so, returns some other text string).

Hopefully I didn't make that even muddier :)
Thanks all!
 
Upvote 0
Sorry if my reply was a little confusing.

Basically I was wondering in the example above if I input 2012, garage, Paint in A2, B2 and C2, that cell B5 would return the word "combination" (meaning that a combination of workers, both bill and tom, fill the criteria: painting the garage in 2012).
B5 could return the text "more than 1" or whatever other text we like, I was just trying to give an example of text being returned based on the fact that more than 1 worked was found to match the criteria in A2, B2 and C2.

So the formula in B5 is trying to figure out if just one worker meets the criteria (if so, it returns that worker's name), or if more than one worker meets that criteria (if so, returns some other text string).

Hopefully I didn't make that even muddier :)
Thanks all!

Consider...

Field-1Field-2Field-3Field-4Field-5 1/20121/2012
1/2012bobkitchenelectrical100 garageKitchen
1/2012billgaragepaint50 paintelectrical
1/2012bobkitchenpaint250 combinationbob
1/2012billkitchenpaint300 75125
1/2012tomgaragepaint125
1/2012bobkitchenpaint225
1/2013tomgaragepaint125
1/2013tomkitchenpaint350
1/2012tomkitchendrywall25
1/2012tomgaragepaint75
1/2012bobkitchenelectrical150

<colgroup><col style="width: 48pt;" span="6" width="64"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3242;" width="91"> <col style="width: 48pt;" width="64"> <tbody>
</tbody>


A:E houses the sample you posted.

G:H, the processing which pick out two set of conditions for which the results are calculated.

G4, control+shift+enter, not just enter, copy across, and down:
Rich (BB code):
=IF(SUM(IF(FREQUENCY(IF($B$2:$B$12<>"",
  IF($A$2:$A$12=G1,IF($C$2:$C$12=G2,IF($D$2:$D$12=G3,
  MATCH($B$2:$B$12,$B$2:$B$12,0))))),
  ROW($B$2:$B$12)-ROW($B$2)+1),1))>1,"combination",
  INDEX($B$2:$B$12,MATCH(1,IF($A$2:$A$12=G1,
  IF($C$2:$C$12=G2,IF($D$2:$D$12=G3,1))),0)))

G5, ccontrol+shift+enter, not just enter, copy across, and down:
Rich (BB code):
=MEDIAN(IF($A$2:$A$12=G1,IF($C$2:$C$12=G2,
  IF($D$2:$D$12=G3,$E$2:$E$12))))
 
Upvote 0
Hi,

Is this what you require?....

Excel Workbook
ABCDEFGHIJ
1YearRoomWorkDateWorkerRoomActionCost
22012GaragePaint01/01/2012bobkitchenelectrical100
301/01/2012billgaragepaint50
4CountWhoMedian01/01/2012bobkitchenpaint250
532 Workers7501/01/2012billkitchenpaint300
601/01/2012tomgaragepaint125
701/01/2012bobkitchenpaint225
8WorkerCost01/01/2013tomgaragepaint125
9bill5001/01/2013tomkitchenpaint350
10tom12501/01/2012tomkitchendrywall25
11tom7501/01/2012tomgaragepaint75
1201/01/2012bobkitchenelectrical150
13
Sheet15


OR.....

Excel Workbook
ABCDEFGHIJ
1YearRoomWorkDateWorkerRoomActionCost
22012KitchenDrywall01/01/2012bobkitchenelectrical100
301/01/2012billgaragepaint50
4CountWhoMedian01/01/2012bobkitchenpaint250
51tom2501/01/2012billkitchenpaint300
601/01/2012tomgaragepaint125
701/01/2012bobkitchenpaint225
8WorkerCost01/01/2013tomgaragepaint125
9tom2501/01/2013tomkitchenpaint350
1001/01/2012tomkitchendrywall25
1101/01/2012tomgaragepaint75
1201/01/2012bobkitchenelectrical150
13
Sheet15


I hope that works, but you may want to go with Aladins solution.

Ak
 
Upvote 0
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-
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top