I recently had two sets of data, one a full list of records and the other a list of identifiers for the records that needed to be extracted. Extracting the relevant records from the full list would be a fairly easy programming task, but the data was in Excel and I wanted to try and accomplish the task in that environment. Thankfully this problem has already been solved and the answer posted to the web.

If I have one column of identifiers for the records that need to be extracted (column A, 100 records) and one column with the full list of identifiers (column B, 1000 records) the following formula will indicate which identifiers from column B match an identifier from column A:

=IF(ISNA(MATCH(B1,$A$1:$A$100,0)),"","X")

Just place the formula in its own column and copy down for the length of the full data set. The columns do not need to be in any particular order and you can create a separate worksheet that contains your filter list, keeping your data and your filter separate. You can then AutoFilter on your search column to see the results (and copy/paste to another worksheet if necessary).