Index,Match or If, Ands?

[bmancz]

Please see below for a simplified version of my problem.

I need to find the plan name. I need to find this plan name if the Customer matches and the Date (4/1/2018) is between the Effective Date and Term Date from the other table. In this particular case, I would need to return the plan "B".

Does anyone have any suggestions?

Customer Date Plan?
1234 4/1/2018 ????
Customer Plan Effective Date Term Date
1234 A 1/1/2018 2/28/2018
1234 B 3/1/2018 4/30/2018
1234 C 5/1/2018 6/30/2018
5678 AB 1/1/2018 2/28/2018
5678 BC 3/1/2018 4/30/2018
5678 CD 5/1/2018 6/30/2018

[CK76]

Try...

=LOOKUP(2,1/((C6:C11<=B2)*(D6:D11>=B2)*(A6:A11=A2)),B6:B11)

[bmancz]

That works perfectly it seems. Thank you!

[CK76]

You are welcome and thanks for the rep

If your questions is resolved, please mark the thread as solved by using Thread tool found at top of your initial post.