Searching for Multiple Values

**Templinho** · 04-18-2007, 06:00 AM

I have two Excel worksheets. The first contains a complete list of User IDs (several hundred rows of numeric values listed in one column). The second contains specific IDs (around 80, same format), which must be removed from the first sheet.

Is there a way to search for these values all at once, rather than using Find for each one individually?

Any help would be greatly appreciated.

**Bryan Hessey** · 04-18-2007, 07:15 AM

Originally Posted by Templinho

I have two Excel worksheets. The first contains a complete list of User IDs (several hundred rows of numeric values listed in one column). The second contains specific IDs (around 80, same format), which must be removed from the first sheet.

Is there a way to search for these values all at once, rather than using Find for each one individually?

Any help would be greatly appreciated.

Hi,

on the first sheet, in a spare column, run a VLookup to the second sheet to pickup if the item is to be removed, something like

=If(IsError(Vlookup(A1,sheet2!A:A,1,False)),"",Vlookup(A1,sheet2!A:A,1,False))

and then use the Data, Filter on that column to select all non-blanks and delete those.

clear the column and the filter after the deletes.

hth
---

**Templinho** · 04-18-2007, 11:18 AM

That's great, thanks.

There's also an IF statement I'm having some trouble with. I have two lists of numbers in adjacent columns. I want to find out whether or not each number in column 2 appears anywhere within column 1:

So:

=IF(B1=..., "True", "False")

What is the correct way to specify this range of values? (e.g. cells A1 to A100).