Finding multiple data

[jw279]

Hello All,

I was hoping you might be able to help me?

I have a list of 500 companies and what I want to do is see if they are present in a seperate list I have that is 30,000 rows long. I wanted to know if there is a formula I can use to show me where these 500 companies are in my list of 30,000. I tried MATCH but the problem is I know that some of these 500 companies are represented multiple times within the 30,000, but MATCH only shows me the first location.

Any help would be much appreciated

Thanks

[Pete_UK]

This sounds similar to this thread:

http://www.excelforum.com/excel-form...07#post3304507

from a couple of days ago. Perhaps you can adapt the solution to your own requirements, or post an example workbook.

Hope this helps.

Pete

[Fotis1991]

Perhaps using countif. In a column next to your big list OR using Conditional Formating to highlight these cells(I suggest the first option)...

So your big list is in column H and your small in column A, starting in row 2.

In I2 and copy down use this.

=COUNTIF($A$2:$A$501,h2)>0

[jw279]

Fotis, thank you, however the problem with this is that the companies in the two lists do differ in name slightly due to some of them having PLC at the end and some not. Therefore COUNTIF is not picking them up

I think a formula like Pete suggested could work possibly, however I don't really understand it haha!

[Fotis1991]

I didn't see Pete's link but i am sure that Pete will be able to help you on that..

On the other hand, perhaps we can do this with countif, but i need to see how your data look like.

Can you upload a small sample workbook?

[jw279]

Ok, I have attached a much smaller example. The second list is the longer list that is actually 30,000 rows long.

[Fotis1991]

Not sure for the results...Just a try...

[jw279]

Thanks for your efforts Fotis

I'm not sure this works exactly; nevertheless the formula you have created has amazed me!