Excel tricks

First guest tip, from my colleague Jonathan Luker:

If you nest a MATCH() function inside a ISNA() function, it can make a column of TRUE or FALSE on the new data.

e.g. if I look for matches in ItemID between data in separate tabs:

=ISNA(MATCH(A2, 'Test-jul data'!$A$2:$A$15633, FALSE))

This will scan the list for matching ItemsIDs and put TRUE or FALSE in the cell beside each one.

Copy & paste for full list (takes a while to process of course!).

If you sort on that TRUE or FALSE column, you'll have all the new ones listed together.

There are a bunch of IS functions in Excel:

# Excel ISBLANK Function

# Excel ISERR Function

# Excel ISERROR Function

# Excel ISLOGICAL Function

# Excel ISNA Function

# Excel ISNONTEXT Function

# Excel ISNUMBER Function

# Excel ISREF Function

# Excel ISTEXT Function

Quite handy, especially when used with IF(), MATCH(), VLOOKUP(), etc.