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

1. ## 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..  Reply With Quote

2. ## Re: Vlookup with ifs to compare cells in different rows based on conditions?

Hi,

Would something like this help?...

Sheet15

 A B C D E F G H I J 1 Year Room Work Date Worker Room Action Cost 2 2012 garage Paint 01/01/2012 bob ******* electrical 100 3 01/01/2012 bill garage paint 50 4 Count Median 01/01/2012 bob ******* paint 250 5 3 75 01/01/2012 bill ******* paint 300 6 01/01/2012 tom garage paint 125 7 01/01/2012 bob ******* paint 225 8 Worker Cost 01/01/2013 tom garage paint 125 9 bill 50 01/01/2013 tom ******* paint 350 10 tom 125 01/01/2012 tom ******* drywall 25 11 tom 75 01/01/2012 tom garage paint 75 12 01/01/2012 bob ******* electrical 150 13

 Cell Formula 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  Reply With Quote

3. ## 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!  Reply With Quote

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

 A B C D E F G H I J 1 Year Room Work Date Worker Room Action Cost 2 2012 garage Paint 01/01/2012 bob ******* electrical 100 3 01/01/2012 bill garage paint 50 4 Count Who Median 01/01/2012 bob ******* paint 250 5 1 Bill 50 01/01/2012 bill ******* paint 300 6 01/01/2012 tom garage paint 125 7 01/01/2012 bob ******* paint 225 8 Worker Cost 01/01/2013 tom garage paint 125 9 bill 50 01/01/2013 tom ******* paint 350 10 01/01/2012 tom ******* drywall 25 11 01/01/2012 tom garage paint 75 12 01/01/2012 bob ******* electrical 150 13

 Cell Formula 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  Reply With Quote

5. ## 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?  Reply With Quote

6. ## Re: Vlookup with ifs to compare cells in different rows based on conditions?

Do you mean the formula in A5?

Ak  Reply With Quote

7. ## 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!  Reply With Quote

8. ## Re: Vlookup with ifs to compare cells in different rows based on conditions? Originally Posted by somanyqs 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))))```  Reply With Quote

9. ## Re: Vlookup with ifs to compare cells in different rows based on conditions?

Hi,

Is this what you require?....

Sheet15

 A B C D E F G H I J 1 Year Room Work Date Worker Room Action Cost 2 2012 Garage Paint 01/01/2012 bob ******* electrical 100 3 01/01/2012 bill garage paint 50 4 Count Who Median 01/01/2012 bob ******* paint 250 5 3 2 Workers 75 01/01/2012 bill ******* paint 300 6 01/01/2012 tom garage paint 125 7 01/01/2012 bob ******* paint 225 8 Worker Cost 01/01/2013 tom garage paint 125 9 bill 50 01/01/2013 tom ******* paint 350 10 tom 125 01/01/2012 tom ******* drywall 25 11 tom 75 01/01/2012 tom garage paint 75 12 01/01/2012 bob ******* electrical 150 13

 Cell Formula 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

 A B C D E F G H I J 1 Year Room Work Date Worker Room Action Cost 2 2012 ******* Drywall 01/01/2012 bob ******* electrical 100 3 01/01/2012 bill garage paint 50 4 Count Who Median 01/01/2012 bob ******* paint 250 5 1 tom 25 01/01/2012 bill ******* paint 300 6 01/01/2012 tom garage paint 125 7 01/01/2012 bob ******* paint 225 8 Worker Cost 01/01/2013 tom garage paint 125 9 tom 25 01/01/2013 tom ******* paint 350 10 01/01/2012 tom ******* drywall 25 11 01/01/2012 tom garage paint 75 12 01/01/2012 bob ******* electrical 150 13

 Cell Formula 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  Reply With Quote

10. ## 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-  Reply With Quote

## User Tag List

#### Posting Permissions

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