Look Up Area The top-left section of the spreadsheet is designed to be able to enter a donor's ID, such as ENG- 15, and look up that person's position in the list, display the donor's full name, and display the amount donated this year.
1. Insert the MATCH function in cell B3 to look up the donor ID in cell B2, compare it to the list in the range D8:D35, and then return the donor's position within the list.
2. Insert an INDEX function in cell B4 that uses the range D8:K35, looks up the row position number from the MATCH function result, and then uses the column position number for Full Name.
3. Insert an INDEX function in cell B5 that uses the range D8:K35, looks up the row position number from the MATCH function result, and then uses the column position number for Donation. Format the value as Accounting Number Format with zero decimal places.
Advanced Database Filtering
To analyze the donor records, you are ready to create criteria and output ranges. You will enter conditions to find records for donors to the College of Business who donated $1,000 or more.
4. Copy the range A7:K7 to cell A38 to create the column labels for the criteria range.
5. Copy the column labels to cell A42.
6. Type Business in cell J39 and >=1000 in cell K39.
7. Perform the advanced filter by copying the records to the output area.