Retrieving text from a reference table based on multiple criteria

Hello,

Please see attached a sample workbook, which would involve a complex formula that I am having lots of trouble with.

The goal here is to extract ID's that either changed programs, remained in the same program, or never returned after any of the 4 withdrawals that have occurred.

You will notice two sections. One desired result is to extract the program in which the ID has withdrawn. The other is to extract the program when ID returned.

I tried to make this as clear as possible.

Thank you to whoever can help me out

Thank you Glen,

I will see how the answer you provided works in the complete list of ID's I have.

Please see the updated sample workbook (error on my part). Does this answer your question? If so, shall I re-post my correct workbook? Is there a way I can replace it in my initial post?

Thank you Bo.

I apologize. You are correct, row 14 and row 15 do not belong in reference table or at least should of been different ID's.

Thanks for quick response. Allow to me to review and reply.

Hi Glenn and Bo,

I have attached the wrong file! Using "withdraw" wont work properly, as there are different types of withdrawals that are spelled very different. That is why I updated the file. Please see attached.

Thank you and Glenn I hope you having some more strong coffee!