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..
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

somanyqs

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

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,185
What does A4 = combination mean for it's not in the sample you posted?
 

somanyqs

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,185
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))))
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

somanyqs

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

Watch MrExcel Video

Forum statistics

Threads
1,102,774
Messages
5,488,772
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top