Multiple values (list) to be searched in another worksheet

Hi all,

I am gonna need your valuable assistance for a problem I am dealing with,

In the attachement (sheet: "orig") you will see a list of numbers (column A), Start Dates (column B), End Dates (column C) and from column D-N the names of the offers available for each number. In the sheet: "actual" you are going to see several numbers in column A, the offer date in column B, the actual offer name given in column D and the name of the person who gave the offer in column E.

The problem is that I need in sheet "orig" , column O to put a formula (if possible) which will check if for the specific number an offer of the available ones in columns D-N has been given in sheet "actual" and give the result "yes" if so (crosscheck columns D-N/sheet orig with column D/sheet actual). Plus for the offer to be successful and count as a "yes" the offer date of column B/sheet "actual" should be between the Start Date & End date of columns B & C/sheet orig.

If we have a yes in column O then the name of the person who gave the offer (sheet "actual" ,column "Offer by") to be appeared in column P.

*Maybe we have multiple rows for the same number in sheet "actual" (so I couldn t use the VLOOKUP function).

I tried SUMPRODUCT with OR in it but cause of the multiple values for the same number I couldn't get a result. Plus it took a long time to run for my data.

I am not very familiar with lists in the formulas and I imagine that maybe the solution is simple but I write it wrong..

Any ideas how to make this work?

Any help will be appreciated

Thank you very much for the answer. It seems that it works!

But to be certain I have to use it at work with real data.

I will get back with the final feedback

Thanks again for the quick answer!!

Hello!

The formulas seem to work but there is another issue... The file I needed the formulas for is quite big. Think that in the actual sheet I have thousand of rows which I will have to add more and more every day so as different sheets to be updated (with your formulas).
I put the formulas in the first column and excel started "calculated cells" for 10 min.. So since I had to do the same 9 times, I changed the formula calculation from automatic to manual, put all the formulas where I needed and when I finished I press "Calculate now".

The excel stuck for 50min and I couldn t do anything else

I think Its SUMPRODUCT's fault. Can we use some other function or can we change something in the excel settings so as I can work in another excel when a file is "calculating cells" ?

Is this easy to do? Thank you very much for your help Naveed Raza
I really appreciated.