I tried to search for this and was unable to find it. If someone knows of an existing thread, please link me to it as I don't wish to recreate the wheel.
I am trying to create a formula that takes the name in table 1 and looks it up in table 2. If it finds that name, it should look up the coinciding date in table 1 to determine if it is between the start and end date in table 2.
I have tried several nested if statements combined with vlookup, index match and such and not gotten anywhere.
I would appreciate any assistance. Once I figure the look up, I can combine it with my other formulas.
Table 1
<tbody>
</tbody>
Table 2
<tbody>
</tbody>
I am trying to create a formula that takes the name in table 1 and looks it up in table 2. If it finds that name, it should look up the coinciding date in table 1 to determine if it is between the start and end date in table 2.
I have tried several nested if statements combined with vlookup, index match and such and not gotten anywhere.
I would appreciate any assistance. Once I figure the look up, I can combine it with my other formulas.
Table 1
Name | Date |
Bob | 1/19/2018 |
Bill | 2/20/2018 |
Fred | 1/15/2018 |
Gus | 1/12/2018 |
<tbody>
</tbody>
Table 2
Name | Start | End |
Bob | 1/1/2018 | 2/1/2018 |
Fred | 12/1/2017 | 3/20/2018 |
Shirly | 1/1/2018 | 1/15/2018 |
Suzy | 2/1/2018 | 2/25/2018 |
<tbody>
</tbody>