XL3/XL4: Including or Excluding a List of Records in Extract

 Last reviewed: February 2, 1998
Article ID: Q103708
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 where you have a list of matches that you want to include or exclude in your final extract, use the MATCH() function as a computed criterion.

To include a list of matches, use the following formula: 

   =MATCH(firstcell,matchlist,0)
To exclude a list of matches, use the following formula 

   =ISNA(MATCH(firstcell,matchlist,0))
where firstcell is the first cell in the field of your database that you want to match.

MORE INFORMATION

When you have only a few matches for a given field, you can set up an "and" relationship using comparison criteria by entering an instance of the field name for each match you want to make. For example, if one of the fields in your database is a Name field and you want to find or extract a specific list of names, the criteria range (F1:H2) might appear as follows: 

   F1: Name   G1: Name   H1: Name
   F2: Ann    G2: Fred   H2: Bob
Or, you can use the AND() function as a computed criterion as follows 

   F1: CompCrit
   F2: =AND(A2="Ann",A2="Fred",A2="Bob")
where column A contains the Name field in your database, cell A2 contains the first name in the field, and the criteria range is defined as F1:F2.

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.

If, however, you have several names that you want to match, you can list them in a vertical cell range as follows: 

   H1: Ann
   H2: Fred
   H3: Bob
   H4: Kristi
   H5: Nikki
You can then use the MATCH() function as a computed criterion, as in the following example (where cells F1:F2 are the criteria range): 

   F1:
   F2: =MATCH(A2,$H$1:$H$5,0)
NOTE: Your matchlist must be an absolute reference or a defined name.

To extract all of the records EXCEPT those in H1:H5, nest the MATCH() function within the ISNA() function.

REFERENCES

"User's Guide 1," version 4.0, pages 330-337 "Function Reference," version 4.0, pages 248, 267

Additional query words: 3.00 4.00 4.00a howto exclusionary exception
exceptional XL4 XL3
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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.