Complex lookup problem based on conditions

T

tonyg88

New Member
Joined
Jan 18, 2015
Messages
12
My goal is to fill out the third column in the second table and identify whether an ID has been completed or not.

I want to find the ID from table 2 in table 1, then pull the value in the completed column in table 1, but only if it's later than the date in table 2. If I can also pull the date / time from table 1 for the completed records, that would be a bonus.

Example:
  • The completed column in table 2 for ID 1 should be 'pending' or error, because the date for that ID in table 2 is later than the date in table 1.
  • The completed column in table 2 for ID 3 should be 'completed', because at least one record is completed in table 1. (the completed date in column 4 would be 8/30/2019. Note: it's completed even the latest record is 'pending'. If at least one completed record is after the date listed on table 2, it should be listed as completed.

This is not the real data, I simplified this for the purpose of posting on here. The real data is contained in a very large excel sheet.

Table 1:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Completed[/TD]
[TD]ID[/TD]
[TD]Date / Time[/TD]
[/TR]
[TR]
[TD]completed[/TD]
[TD]1[/TD]
[TD]8/1/2019[/TD]
[/TR]
[TR]
[TD]pending[/TD]
[TD]2[/TD]
[TD]8/5/2019[/TD]
[/TR]
[TR]
[TD]completed[/TD]
[TD]3[/TD]
[TD]7/2/2019[/TD]
[/TR]
[TR]
[TD]pending[/TD]
[TD]3[/TD]
[TD]8/5/2019[/TD]
[/TR]
[TR]
[TD]completed[/TD]
[TD]3[/TD]
[TD]8/30/2019[/TD]
[/TR]
[TR]
[TD]pending[/TD]
[TD]3[/TD]
[TD]8/31/2019[/TD]
[/TR]
[TR]
[TD]cancelled[/TD]
[TD]4[/TD]
[TD]9/5/2019[/TD]
[/TR]
</tbody>[/TABLE]

Table 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date / Time[/TD]
[TD]Completed in Table 1?[/TD]
[TD]Table 1 Date / Time?[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/29/2019[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8/29/2019[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8/1/2019[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9/5/2019[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Hi tonyg88,

I'm only retrieving the date field because if you only want to see "completed" then I can do that in TABLE 2 by checking if a date was retrieved with the ISNUMBER.

If there are multiple "completed" entries for later dates for that Id then I'm retrieving the first. If you want the last then change the AGGREGATE SMALL (15) to LARGE (14). If you want all of them then I'll need a worker column.

ABCDEFGHIJ
Table 1:Table 2:
CompletedIDIDCompleted in Table 1?
completed
pending
completedcompleted
pending
completed
pending
cancelled

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Date / Time[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Date / Time[/TD]

[TD="align: center"]Table 1 Date / Time?[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: center"]01-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]29-Aug-19[/TD]

[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: center"]05-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]29-Aug-19[/TD]

[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]02-Jul-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]01-Aug-19[/TD]

[TD="align: center"]30-Aug-19[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]05-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]05-Sep-19[/TD]

[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]30-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]31-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]4[/TD]
[TD="align: center"]05-Sep-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=IF(ISNUMBER(J4),"completed","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4
[/TH]
[TD="align: left"]=IFERROR(INDEX($C$4:$C$18,AGGREGATE(15,6,ROW($B$4:$B$18)-ROW($B$3)/(($B$4:$B$18=G4)*($C$4:$C$18>H4)*($A$4:$A$18="completed")),1)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
@Toadstool - awesome, thanks! This works, just one more question --> I forgot to mention that 'cancelled' should also be considered as completed. What's the best way to add that into the formula?
 
Upvote 0
You can also try this:


Book1
ABCD
1Table 1:
2
3CompletedIDDate / Time
4completed101-08-2019
5pending205-08-2019
6completed302-07-2019
7pending305-08-2019
8completed330-08-2019
9pending331-08-2019
10cancelled405-09-2019
11
12Table 2:
13
14IDDate / TimeCompleted in Table 1?Table 1 Date / Time?
15129-08-2019  
16229-08-2019
17301-08-2019Completed30-08-2019
18405-09-2019
19
20
Sheet2
Cell Formulas
RangeFormula
C15{=IF(SUM(--(B15($B$4:$B$10=A15)*(($A$4:$A$10="completed")+($A$4:$A$10="cancelled")),$C$4:$C$10,0))),"Completed","")}
D15{=IF(C15="","",MAX(IF(B15($B$4:$B$10=A15)*(($A$4:$A$10="completed")+($A$4:$A$10="cancelled")),$C$4:$C$10,0),$C$4:$C$10,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
tonyg88 said:
@Toadstool - awesome, thanks! This works, just one more question --> I forgot to mention that 'cancelled' should also be considered as completed. What's the best way to add that into the formula?
Click to expand...

completed and cancelled both start with a "c" so I'll check that. Unfortunately now I also need AGGREGATE in column "I" because I can no longer tell from the presence of a date that it's "completed". I added another row in TABLE 2 just to show a "cancelled" result

ABCDEFGHIJ
Table 1:Table 2:
CompletedIDIDCompleted in Table 1?
completed
pending
completedcompleted
pending
completedcancelled
pending
cancelled

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Date / Time[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Date / Time[/TD]

[TD="align: center"]Table 1 Date / Time?[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: center"]01-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]29-Aug-19[/TD]

[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: center"]05-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]29-Aug-19[/TD]

[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]02-Jul-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]01-Aug-19[/TD]

[TD="align: center"]30-Aug-19[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]05-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]05-Sep-19[/TD]

[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]30-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]04-Sep-19[/TD]

[TD="align: center"]05-Sep-19[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]31-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]4[/TD]
[TD="align: center"]05-Sep-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=IFERROR(INDEX($A$4:$A$18,AGGREGATE(15,6,ROW($B$4:$B$18)-ROW($B$3)/(($B$4:$B$18=G4)*($C$4:$C$18>H4)*(LEFT($A$4:$A$18,1)="c")),1)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4
[/TH]
[TD="align: left"]=IFERROR(INDEX($C$4:$C$18,AGGREGATE(15,6,ROW($B$4:$B$18)-ROW($B$3)/(($B$4:$B$18=G4)*($C$4:$C$18>H4)*(LEFT($A$4:$A$18,1)="c")),1)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Capture Dates
2
Replies
19
Views
295
dreid1011
dreid1011
O
  • Question Question
Limit filter list to unique values
3 4 5
Replies
40
Views
1K
ollyhughes1982
O
F
  • Question Question
Stop counting dates when date complete cell is filled?
Replies
7
Views
166
Finlaysm
F
N
  • Solved
Count days between to Table Ranges that falls in SLA intervals
Replies
4
Views
122
Nicha
N
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
607
Domenic
D

Forum statistics

Threads
1,223,885
Messages
6,175,182
Members
452,615
Latest member
bogeys2birdies

Share this page

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