Searching for more than 1 term

First post, sorry if this is the wrong section.

I have a question about how to go about this big search I want to do in Excel. First let me explain the situation.

I have 2 spreadsheets:

#1 contains about 30,000 rows, each one is the name of a company.
#2 contains about 18,000 rows, each one is the name of a company too.

I know for a fact that #1 contains all of #2, most of the names being exact character matches (if I'm not being case sensitive).

What I want to do is create an extra column in #1 with only one of two values: 'yes' or 'no'. 'Yes' means this row exists in #2, and 'no' obviously those that aren't in #2.

My question: how do I get Excel to do this automatically, to save me doing ctrl-F and searching for each of the 18,000 names individually?

Many thanks in advance to whoever will save me hours of tedious work...

ps I have Excel 2010 not 2003 as is says in my profile.

Try something like:

=IF(Isnumber(Match(A2,'Sheet2'!A:A,0)),"Yes","No")

where A2 on current sheet is being searched in column A of Sheet2... adjust as necessary and copy down the list

All you have to do is copy my formula to cell that in a free column and in the same row as the first item to look up in the other table.

Adjust the A2 in the formula to correspond to the cell reference that the first lookup item is in. Then adjust the Sheetname between single quotes to exactly match the sheet name the table is in. And change the A:A to the column reference that the match is supposed to be in...

Then hit Enter.. and hopefully you get the expected result.

If satisfied, click and hold the little black square at the bottom right corner of the cell with the formula in it... and drag down the list... all the formula should auto adjust to corresponding row items.

You can use C:C (that refers to the whole column) or you can use a more defined range like $C$1:$C$18000

Ok, so it's not working for me at the moment. This is what the formula looks like:

=IF(ISNUMBER(MATCH(B10,'[Sheet2]Batch 1'!C:C,0)),"Yes","No")

where B10 is the first of the 30,000 names, amongst which there are 18,000 which are in column C of Sheet 2, in workbook Batch 1.

It's returning a 'No' even though I did a manual Ctrl F and the name is there both in Sheet 2 and B10.

Any idea where it went wrong?

ps I'm very grateful for your help.



Edit: when I press Enter after entering the formula, Excel opens a dialog box named 'update sheet values', so I click on Sheet 2. It then returned a correct 'Yes' for cell B10 but after dragging the corner of the cell down to repeat the formula, it has returned a 'Yes' incorrectly. The name is a partial match only, does this formula count partial matches as 'true'?

Are you sure about the naming: '[Sheet2]Batch 1'!?

Are you working within one workbook? If so, you need only specify the actual sheet name, which I assume might be Batch 1

So try:

=IF(ISNUMBER(MATCH(B10,'Batch 1'!C:C,0)),"Yes","No")

This formula finds exact matches only... if you want partial matches, we can add wildcards...

Go to Tools|Options and in the Calculation tab, ensure that the Automatic checkbox is ticked....

For checking two columns, try:

=IF(ISNUMBER(MATCH(B10,'Batch 1'!C:C,0)),"Yes",IF(ISNUMBER(MATCH(B10,'Batch 1'!D:D,0)),"Yes","No"))

There are a couple of preferred charities in my signature below.....

Thank you!