Fine-Tuning Excel Database Criteria with AND and OR
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.
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.
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.
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.