XL: Excluding a List of Records in ExtractID: Q103708
|
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(<firstcell>,<excludelist>,0))
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
"User's Guide 1," version 4.0, pages 330-337
"Function Reference," version 4.0, pages 248, 267
Additional query words: 4.00a howto exclusionary exception exceptional XL4 XL3
Keywords :
Version : WINDOWS:3.0,4.0,4.0a; OS/2:3.0; MACINTOSH:3.0,4.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo
Last Reviewed: April 6, 1999