Complex lookup problem based on conditions

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:

CompletedIDDate / Time
completed18/1/2019
pending28/5/2019
completed37/2/2019
pending38/5/2019
completed38/30/2019
pending38/31/2019
cancelled49/5/2019

<tbody>
</tbody>

Table 2:

IDDate / TimeCompleted in Table 1?Table 1 Date / Time?
18/29/2019??
28/29/2019??
38/1/2019??
49/5/2019??

<tbody>
</tbody>
 
Last edited:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
1Table 1:Table 2:
2
3CompletedIDDate / TimeIDDate / TimeCompleted in Table 1?Table 1 Date / Time?
4completed101-Aug-19129-Aug-19
5pending205-Aug-19229-Aug-19
6completed302-Jul-19301-Aug-19completed30-Aug-19
7pending305-Aug-19405-Sep-19
8completed330-Aug-19
9pending331-Aug-19
10cancelled405-Sep-19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I4=IF(ISNUMBER(J4),"completed","")
J4
=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)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
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
@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?

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
1Table 1:Table 2:
2
3CompletedIDDate / TimeIDDate / TimeCompleted in Table 1?Table 1 Date / Time?
4completed101-Aug-19129-Aug-19
5pending205-Aug-19229-Aug-19
6completed302-Jul-19301-Aug-19completed30-Aug-19
7pending305-Aug-19405-Sep-19
8completed330-Aug-19404-Sep-19cancelled05-Sep-19
9pending331-Aug-19
10cancelled405-Sep-19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1 (2)

Worksheet Formulas
CellFormula
I4=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)),"")
J4
=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)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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