Vlookup with ifs to compare cells in different rows based on conditions?
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Vlookup with ifs to compare cells in different rows based on conditions?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Location
    Pleasanton, CA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 (*******, 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 ******* electrical 100
    1/2012 bill garage paint 50
    1/2012 bob ******* paint 250
    1/2012 bill ******* paint 300
    1/2012 tom garage paint 125
    1/2012 bob ******* paint 225
    1/2013 tom garage paint 125
    1/2013 tom ******* paint 350
    1/2012 tom ******* drywall 25
    1/2012 tom garage paint 75
    1/2012 bob ******* electrical 150

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

    Or
    A1 = 1/2012
    A2 = *******
    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..

  2. #2
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi,

    Would something like this help?...

    Sheet15

     ABCDEFGHIJ
    1YearRoomWork DateWorkerRoomActionCost 
    22012garagePaint 01/01/2012bob*******electrical100 
    3    01/01/2012billgaragepaint50 
    4Count Median 01/01/2012bob*******paint250 
    53 75 01/01/2012bill*******paint300 
    6    01/01/2012tomgaragepaint125 
    7    01/01/2012bob*******paint225 
    8WorkerCost  01/01/2013tomgaragepaint125 
    9bill50  01/01/2013tom*******paint350 
    10tom125  01/01/2012tom*******drywall25 
    11tom75  01/01/2012tomgaragepaint75 
    12    01/01/2012bob*******electrical150 
    13          

    Spreadsheet Formulas
    CellFormula
    A5=SUMPRODUCT(--(YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2))
    C5=MEDIAN(B9:B11)
    A9{=IF(ROWS(A$9:A9)<=$A$5,INDEX($F$2:$F$12,SMALL(IF((YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2),ROW($F$2:$F$12)),ROWS(A$9:A9))-ROW($F$2)+1),"")}
    B9{=IF(ROWS(B$9:B9)<=$A$5,INDEX($I$2:$I$12,SMALL(IF((YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2),ROW($I$2:$I$12)),ROWS(B$9:B9))-ROW($F$2)+1),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

    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
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    Pleasanton, CA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  4. #4
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi,

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

    Is this what you want?...

    Sheet15

     ABCDEFGHIJ
    1YearRoomWork DateWorkerRoomActionCost 
    22012garagePaint 01/01/2012bob*******electrical100 
    3    01/01/2012billgaragepaint50 
    4CountWhoMedian 01/01/2012bob*******paint250 
    51Bill50 01/01/2012bill*******paint300 
    6    01/01/2012tomgaragepaint125 
    7    01/01/2012bob*******paint225 
    8WorkerCost  01/01/2013tomgaragepaint125 
    9bill50  01/01/2013tom*******paint350 
    10    01/01/2012tom*******drywall25 
    11    01/01/2012tomgaragepaint75 
    12    01/01/2012bob*******electrical150 
    13          

    Spreadsheet Formulas
    CellFormula
    A5=SUMPRODUCT(--(YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2)*($F$2:$F$12=$B$5))
    C5=MEDIAN(B9:B11)
    A9{=IF(ROWS(A$9:A9)<=$A$5,INDEX($F$2:$F$12,SMALL(IF((YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2)*($F$2:$F$12=$B$5),ROW($F$2:$F$12)),ROWS(A$9:A9))-ROW($F$2)+1),"")}
    B9{=IF(ROWS(B$9:B9)<=$A$5,INDEX($I$2:$I$12,SMALL(IF((YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2)*($F$2:$F$12=$B$5),ROW($I$2:$I$12)),ROWS(B$9:B9))-ROW($F$2)+1),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

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

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

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

    What does A4 = combination mean for it's not in the sample you posted?
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Aladin,

    Do you mean the formula in A5?

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Location
    Pleasanton, CA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

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

    Quote Originally Posted by somanyqs View Post
    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-1 Field-2 Field-3 Field-4 Field-5 1/2012 1/2012
    1/2012 bob ******* electrical 100 garage *******
    1/2012 bill garage paint 50 paint electrical
    1/2012 bob ******* paint 250 combination bob
    1/2012 bill ******* paint 300 75 125
    1/2012 tom garage paint 125
    1/2012 bob ******* paint 225
    1/2013 tom garage paint 125
    1/2013 tom ******* paint 350
    1/2012 tom ******* drywall 25
    1/2012 tom garage paint 75
    1/2012 bob ******* electrical 150


    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:
    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:
    Code:
    =MEDIAN(IF($A$2:$A$12=G1,IF($C$2:$C$12=G2,
      IF($D$2:$D$12=G3,$E$2:$E$12))))
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi,

    Is this what you require?....

    Sheet15

     ABCDEFGHIJ
    1YearRoomWork DateWorkerRoomActionCost 
    22012GaragePaint 01/01/2012bob*******electrical100 
    3    01/01/2012billgaragepaint50 
    4CountWhoMedian 01/01/2012bob*******paint250 
    532 Workers75 01/01/2012bill*******paint300 
    6    01/01/2012tomgaragepaint125 
    7    01/01/2012bob*******paint225 
    8WorkerCost  01/01/2013tomgaragepaint125 
    9bill50  01/01/2013tom*******paint350 
    10tom125  01/01/2012tom*******drywall25 
    11tom75  01/01/2012tomgaragepaint75 
    12    01/01/2012bob*******electrical150 
    13          

    Spreadsheet Formulas
    CellFormula
    A5=SUMPRODUCT(--(YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2))
    B5=IF(SUMPRODUCT((A9:A15<>"")/COUNTIF(A9:A15,A9:A15&""))>1,SUMPRODUCT((A9:A15<>"")/COUNTIF(A9:A15,A9:A15&""))&" Workers",A9)
    C5=MEDIAN(B9:B11)
    A9{=IF(ROWS(A$9:A9)<=$A$5,INDEX($F$2:$F$12,SMALL(IF((YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2),ROW($F$2:$F$12)),ROWS(A$9:A9))-ROW($F$2)+1),"")}
    B9{=IF(ROWS(B$9:B9)<=$A$5,INDEX($I$2:$I$12,SMALL(IF((YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2),ROW($I$2:$I$12)),ROWS(B$9:B9))-ROW($F$2)+1),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

    OR.....

    Sheet15

     ABCDEFGHIJ
    1YearRoomWork DateWorkerRoomActionCost 
    22012*******Drywall 01/01/2012bob*******electrical100 
    3    01/01/2012billgaragepaint50 
    4CountWhoMedian 01/01/2012bob*******paint250 
    51tom25 01/01/2012bill*******paint300 
    6    01/01/2012tomgaragepaint125 
    7    01/01/2012bob*******paint225 
    8WorkerCost  01/01/2013tomgaragepaint125 
    9tom25  01/01/2013tom*******paint350 
    10    01/01/2012tom*******drywall25 
    11    01/01/2012tomgaragepaint75 
    12    01/01/2012bob*******electrical150 
    13          

    Spreadsheet Formulas
    CellFormula
    A5=SUMPRODUCT(--(YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2))
    B5=IF(SUMPRODUCT((A9:A15<>"")/COUNTIF(A9:A15,A9:A15&""))>1,SUMPRODUCT((A9:A15<>"")/COUNTIF(A9:A15,A9:A15&""))&" Workers",A9)
    C5=MEDIAN(B9:B11)
    A9{=IF(ROWS(A$9:A9)<=$A$5,INDEX($F$2:$F$12,SMALL(IF((YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2),ROW($F$2:$F$12)),ROWS(A$9:A9))-ROW($F$2)+1),"")}
    B9{=IF(ROWS(B$9:B9)<=$A$5,INDEX($I$2:$I$12,SMALL(IF((YEAR($E$2:$E$12)=$A$2)*($G$2:$G$12=$B$2)*($H$2:$H$12=$C$2),ROW($I$2:$I$12)),ROWS(B$9:B9))-ROW($F$2)+1),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

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

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  10. #10
    Board Regular
    Join Date
    Oct 2007
    Location
    Pleasanton, CA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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-

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •