How can I search one tab against information from another tab

I have a large spreadsheet containing thousands of medical provider names on one tab, and about 100 retained experts on another tab. How can I get Excel to search the first tab of thousands of providers using what is in the 2nd tab of retained experts?

My ideas: could I record a macro of me searching the chart with each name, and for any matches have the cell turn yellow? That seems rather time consuming to record the macro.

I thought maybe a vlookup table, but not sure how I would set that up.

Any help would be appreciated!

Assuming your medical provider names are in Sheet1, column A, and your experts are in Sheet2, column A (starting in A1), then highlight Sheet2 column A and use Conditional Formatting with the formula:

Please Login or Register  to view this content.

Set the formatting to fill yellow (or whatever you like).

Of course, this looks for an exact match ...

I'm sorry, I'm usually not this slow, but for some reason I'm just not following. I have the experts (well just a few for this test, but imagine I have 200) in sheet2 column a, and all the providers in sheet1 column a. I would want for Excel to take the experts list in sheet2 and if it finds a match in sheet1, highlight it.

Thank you for your reply, and time. I appreciate it.

Yes, that's what this does. It uses Conditional Formatting triggered by the formula I gave you. Why don't you create a file with a small subset of your data and upload it and I'll set it up so you can see how it works.

Ok I've attached a small set. Ideally we have a list of providers listed "Lastname, Firstname" is Sheet1, Column A. I'd like for our retained experts to be on Sheet2, Expert lastname in column a and expert firstname in column b. I'd like for it to search Sheet one for the last name, and change the font to red. Then, I'd also like for it to take any potential expert names on Sheet3, potential lastname in column a and potential firstname in column b, and search sheet1 column a for any matches. if it finds any, highlight it yellow. The lists on all 3 sheets will grow and change week-to-week, so I'll need to take that into account.

Thanks so much!

Ok, try the attached. Because your Provider sheet shows Last Name, First Name and you've not only separated the two in the other sheet but there is the issue of Brian Flynn versus Brian J. Flynn (lookups need to match unless you use wildcards), I added helper columns C in the other two tabs. You can hide them if you want. Does this all work for you?