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:

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
533
Office Version
2016
Platform
Windows
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>
 

tonyg88

New Member
Joined
Jan 18, 2015
Messages
12
@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?
 

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
507
You can also try this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Table 1:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;;">Completed</td><td style="font-weight: bold;;">ID</td><td style="font-weight: bold;;">Date / Time</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">completed</td><td style="text-align: right;;">1</td><td style="text-align: right;;">01-08-2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">pending</td><td style="text-align: right;;">2</td><td style="text-align: right;;">05-08-2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">completed</td><td style="text-align: right;;">3</td><td style="text-align: right;;">02-07-2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">pending</td><td style="text-align: right;;">3</td><td style="text-align: right;;">05-08-2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">completed</td><td style="text-align: right;;">3</td><td style="text-align: right;;">30-08-2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">pending</td><td style="text-align: right;;">3</td><td style="text-align: right;;">31-08-2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">cancelled</td><td style="text-align: right;;">4</td><td style="text-align: right;;">05-09-2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Table 2:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="font-weight: bold;;">ID</td><td style="font-weight: bold;;">Date / Time</td><td style="font-weight: bold;;">Completed in Table 1?</td><td style="font-weight: bold;;">Table 1 Date / Time?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;">29-08-2019</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">2</td><td style="text-align: right;;">29-08-2019</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">3</td><td style="text-align: right;;">01-08-2019</td><td style=";">Completed</td><td style="text-align: right;;">30-08-2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">4</td><td style="text-align: right;;">05-09-2019</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C15</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">--(<font color="Green">B15<IF(<font color="Purple">(<font color="Teal">$B$4:$B$10=A15</font>)*(<font color="Teal">(<font color="#FF00FF">$A$4:$A$10="completed"</font>)+(<font color="#FF00FF">$A$4:$A$10="cancelled"</font>)</font>),$C$4:$C$10,0</font>)</font>)</font>),"Completed",""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D15</th><td style="text-align:left">{=IF(<font color="Blue">C15="","",MAX(<font color="Red">IF(<font color="Green">B15<IF(<font color="Purple">(<font color="Teal">$B$4:$B$10=A15</font>)*(<font color="Teal">(<font color="#FF00FF">$A$4:$A$10="completed"</font>)+(<font color="#FF00FF">$A$4:$A$10="cancelled"</font>)</font>),$C$4:$C$10,0</font>),$C$4:$C$10,0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
533
Office Version
2016
Platform
Windows
@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>
 

Forum statistics

Threads
1,089,437
Messages
5,408,218
Members
403,191
Latest member
fmstation

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top