Hi - Dave here.
Happy Friday!
Today, I have another cool example of how you can use the FILTER function to do something useful.
In the workbook below, the goal is to list common values in List 1 and List 2 as shown. The solution is based on the FILTER function and the XMATCH function. The formula in cell F5 looks like this:
=FILTER(B5:B16,ISNUMBER(XMATCH(B5:B16,D5:D14)))
[
Download the workbook and read the full explanation]
The article above contains a full explanation. Although the lists in this example are quite short to make the problem easy to understand, the same approach will work for lists that contain hundreds or thousands of values. You can easily adapt this formula to do things like:
- List people who attended two different events
- List people who donated this year and last year
- List correct guesses compared to actual results
Download the workbook and try it out yourself.
