Microsoft KB Archive/103708

{|
 * width="100%"|

XL: Excluding a List of Records in Extract

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0, 4.0a
 * Microsoft Excel for OS/2, version 3.0

-

SUMMARY
When you extract records from a database in Microsoft Excel, you specify criteria that you want the extracted records to meet. In cases in which you have a list that you want to exclude in your results, you can use the MATCH and ISNA functions as a computed criterion.

To exclude a list, use the following formula

  =ISNA(MATCH(, ,0))

where is the first cell in the field of your database that you want to extract, and is a list of entries that you want to exclude.

MORE INFORMATION
In the following example, to extract all of the records EXCEPT those that match C2:C4, use the ISNA and MATCHfunctions as a computed criterion.

  Database          ExcludeList       Criteria --

A1: Name         C1:               F1: CompCrit A2: Ann          C2: Ann           F2: =ISNA(MATCH(A2,$C$2:$C$4,0)) A3: Fred         C3: Bob A4: Bob          C4: Nikki A5: Kristi A6: Nikki

NOTE: Your reference to the list of entries that you want to exclude must be an absolute reference or a defined name.

Note that when you specify a computed criterion, the cell above the formula cannot contain the actual field name; it must either be blank or contain a different text string.