AbleOwl

List items in one list not in the other

There are lists A and B below. The task is to list the items that are in both lists, in B but not in A and in A but not in B. In T15, X15 and AA15, there are spill formulas that return those lists. The list A and B source data are in Excel Tables named dtbListA and dtbListB. If List A and List B changes in content or length, the output changes automatically.

The formulas use MATCH. In Q15, MATCH searches for Queso in dtbListA[Product], which is the range K15:K39. It finds a match in position 17, the 17th cell of the range searched. The first argument of MATCH, that which it searches for, is a range and, so, it returns a range of results, which spills into the cells below. Where there is no match, MATCH returns /A.

In Q15, INDEX returns the value from the cell in the17th position of dtbListA[Value], that is, from L31.

Note that the second argument is Q15 #. The # signifies that the reference is to the whole spill range that starts at Q15. You could instead include Q15:Q27, however, Q15 # is preferable because if the range becomes longer, Q15;Q27 remains as so, but Q15 # refers to the whole range.

You might think that you could make Q15:Q27 automatically extend if you made the output range Q14:R27 an Excel Table, but, alas, you cannot have spill formulas in a Table.

The /A rows are not wanted. The solution is to combine FILTER, ISNUMBER and MATCH as in T15. ISNUMBER returns an array of TRUEs and FALSEs. FILTER has two arguments. Where the corresponding value in the second argument is TRUE, it returns the row range of the first argument. Hence, T15 returns just the non- /A rows from Q15:R27.

V15 needs another spill formula. It has =INDEX(dtbListA[Value],FILTER(MATCH(dtbListB[Product],dtbListA[Product],0),ISNUMBER(MATCH(dtbListB[Product],dtbListA[Product],0))))

MATCH returns the match positions. FILTER returns only those that are not /A. INDEX returns the values of L15:L39 at the match positions.

Excel Tip #926 - INDEX to return more than one item

The INDEX function can return more than one item. Usually we use the syntax INDEX(array,row_num,col_num) and the function would return a single item. For example, INDEX({10,20,30,40,50},2,1) would return 20.

However, you can pass an array as a row argument, and INDEX would return each item in that row array. INDEX(your range,{1,3,5}) would return the 1st, 3rd and 5th items, for example. The function returns a Spilled Range, so you just have to make sure that, in this case, there are three blank cells, and your INDEX formula just goes in the first of those cells.

It's a really handy capability, especially if you combine INDEX with the SEQUENCE function (which creates an array). It gives you the ability, for example, to select the top n items in a table. Suppose you were a woodworker with a project away from your workshop and could only take your 3 sharpest chisels with you, you'd simply combine the INDEX, SORT and SEQUENCE functions. The image below shows you how.

And if you find out you've room for one more chisel, then just change that 3 to a 4, and you now have your 4 sharpest chisels ... no change to formulas is required.

Of course, you could change the SORT parameter from -1 to 1, and now you've got yourself a weekend task of sharpening your n dullest chisels - better get those water stones ready!

Where I've found this feature really useful is in grabbing certain columns for a table, but not the entire table. I can simply enter the column indexes I'm after in an INDEX(col_array) call. Or as in the example above when I want to grab data from a dynamic filter.