ExceL University: comparing spreadsheet lists with ease.

• Author: Lenning, Jeff
• Position: Softwaretips

have you ever had two lists in Excel and wondered what items on one list appear on the other? For example, you've exported a check register from your accounting system and downloaded activity from your bank's website, and you simply wanted LO know which checks cleared the bank. That is. which checks on the checklist also appear on the bank download? We'll refer to this idea as list comparisons and. fortunately Excel is a pro at them.

List Comparisons

For our purpose, a list comparison is the task ol comparing two Excel lists to find which items are the same or different between them. Consider the two lists in Figure 1. Our objective is to quickly determine which checks On the IcR side, the cheek register also) appear 0n the right side. the bank download.

Since this is Excel. there are many options available. In this article. well discuss my Favorite Functions for perrming list comparisons: COUNTIFS and IF

COUNTIFS

This is a multiple-condition, condition. function. Waait, waht? It's counting function because it counts the number of cells in a range. but it only includes those rows that meet one or more conditions, The syntax for the function follows:

=COUNTIFS (criteria_range1, criteria 1,...)

Where:

* Criteria_rangel is I he range of cells to count.

* Criteria_is the criteria the cell values must meet to be included in the count.

* ... means up to 127 conditions may be tested.

Let's see how we can use this

conditional counting function to help

with a list comparison.

we can write a formula next to the check register list that counts how many times each check number appears in the bank download list. II the formula counts zero, then we determine the check is outstanding. If the formula returns one, then we determine die cheek has cleared the bank. The fid lowing formula written into cell E11 and filled clown would provide such a result:

=COUNTIFS($G$11:$G$18,B11)

Where:

* $G$11:$G$18 is the bank download cheek number range.

* Bll is die check register check number we are seeking.

Now. lei me ask you a question:

What if. instead of returning the number of matching rows, we wanted to return

something else, such as

"Cleared" or "Outstanding."

This is where our friend IF can help.

IF

The IF function returns a value based on the result of a test. The syntax follows:

=IF(logical_test, [value_if_true], [value_if_false])

Where:

* logical test is the expression lo test.

* [value_if_true] is the value to return if logical_test is TRUE.

*...