Fine-Tuning Excel Database Criteria with AND and OR

By Ken Bluttman

Excel’s database functions would not be of much use if you could not create fairly sophisticated queries. A few common types of queries are as follows:

  • Records that match two or more individual criteria

  • Records that match any one of several criteria

  • Values that fall within a specified range

To find records that match two or more criteria, place the criteria in adjacent columns in the criteria area. Working with a student-grade database, the criteria area shown here matches records where the Class field contains Accounting 101 and the Teacher field contains Mr. Harris. This is called an AND criterion.

Finding records that match two criteria.
Finding records that match two criteria.

To match records that meet any one of several criteria, place the individual criteria in two or more rows below the field name. The following figure shows a criteria range that matches all records where the Class field contains either Accounting 101 or English Literature. This is called an OR criterion.

Finding records that match any one of two or more criteria.
Finding records that match any one of two or more criteria.

To combine AND with OR in a criteria range, use two or more columns and two or more rows. The following figure shows a criteria range that finds all records where Class is Accounting 101 and Teacher is either Mr. Harris or Mr. Richards.

Combining AND and OR criteria.
Combining AND and OR criteria.

To define a criterion that uses ranges, use these numerical comparison operators:

  • < for less than

  • > for greater than

  • <= for less than or equal to

  • >= for greater than or equal to

Of course, you can apply these to fields with numerical values. The following figure shows two criteria areas. The upper one matches all records in which Final Grade is 90 or higher. The lower one matches all records in which Final Grade is equal to or greater than 80 and less than 90.

Defining numerical range criteria.
Defining numerical range criteria.