List Differences in Two Cell Ranges in Google Spreadsheet
Another tip from my recent work in Google Spreadsheets.
I had two columns of data and was looking for a way to display items that appeared in one column that didn’t appear in the other. The combination of the FILTER and MATCH functions is how I accomplished this.
The FILTER function returns items from a source range that meets a specific criteria. The first argument in the FILTER function is the source range. The second argument (and further optional arguments) is the criteria. The example given by Google is as follows:
=FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)
In this example items from range A2:B26 will appear if their value in column A is greater than 5 and their value in column D is less than 10.
This was only part of my solution. I still needed to compare my two columns as the criteria of the FILTER function. I did this using the MATCH function. MATCH is very similar to FILTER. It returns the position in a range based on a criteria. For example if you have a list of the days of the week days (Sunday, Monday, Tuesday, etc.) and your criteria was "Wednesday" you end up with this:
=MATCH("Wednesday",I8:I16,0)
"Wednesday" is your criteria, A1:A7 is your range, 0 is your search type (exact match when range is unsorted). This will return 4 since Wednesday would be fourth in your list.
Throw in ISERROR to check whether MATCH is returning an error and you have the following:
=FILTER(A1:A7,ISERROR(MATCH(A1:A7,B1:B7,0)))
In this example columns A and B both include a list of the days of the week. If column B is missing a day (or more) this formula will return the missing day(s).
Pretty neat.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.