Search entire sheet for text

Hello,

I have a test that I need to run over 100 times where I drop raw data (a pdf converted to excel) into the data sheet tab on the attached and perform a search based on a list on the List tab to see if it finds a match so that I can flag where matches are missing. It doesn't need to be a loop for 100 times. There would be a separate file for each test. I tried =COUNTIF(Sheet1!$1:$1048576,"*"&'Exhibit 7 - Lease in Place'!D13&"*") but it doesn't seem to be working for many in the list. Any ideas how I can do this? The raw data comes out with many different variations of the converted data and I don't have time to build a macro to clean up the converted pdf info.

I literally just want it to pass or fail. Fail would mean the item in the list is not on the data sheet. And the list can vary in size.

That is correct. Maybe a pass/fail column and a count column to see if it exists more than once?

you're the best. Thanks. I will test it out now.

how would the code change if the text list i am searching is in column D and I want the test performed in column S?

Thank you so much for your help. I get an overflow error at this point in the code when I run it.

NbT = NbT + 1

Thanks. I got an object variable error this last time. I attached a better version of the file. Exhibit 7 column D has a list of text names/tenants that I am trying to see if they are on the second tab

works awesome and pretty quickly too. Thank you so much.