In the office, I sometimes deal with a table of values. In that table, there are good rows, and bad rows.
I want to quickly put the good rows to the top of the table, and leave the bad rows at the bottom of the table.
So I take the first unused column, give it a rubbish column header, such as “qwe”, and build a formula in the next row down ready to autofill.
The formula will return TRUE for the goodies, and FALSE for the baddies.
After an autofill, I’ll sort descending by that column, which puts the goodies at the top.
Usually the formula will be something simple, like
In that way, all of the bad rows can be dealt with as a group, rather than scanning through the rows fixing one at a time.
There’s another one I regularly do. It’s “is this value in my other list”. Kind of like an integrity check.
I’ll have two tables – a primary table and a secondary table. I need to know if rows in my primary table are linked to rows in my secondary table.
For example, Employee not in Attendees list. Purchase Order not in Invoices list.
I’ll build a column on the side of the primary table that returns either TRUE or FALSE and use a lookup formula to do the work.
Once I have a column of TRUEs and FALSEs, it’s a simple matter to sort, then focus on the baddies.
In fact, I do these types of yes/no lookups so often that I remember the pattern to building the formula off by heart. Sad, but true.
1. NOT followed by ISNA followed by MATCH
2. Value to look up
3. List to look up
4. 0 for exact match
5. three closing brackets
6. Highlight the “list to look up” text and hit the F4 key to make it absolute.
Now that I think about it, I’ve probably typed that line of text over 1000 times and I really should write a macro for that.
While I’m writing about the lookup, here’s a way of doing a lookup all without an external list.
It’s possible to bring, for example, B3:B9 into the formula while still retaining the MATCH function – what I call an inline lookup.
Simply highlight the B3:B9 text, and press F9 on your keyboard.
By pressing F9, it brings the list into your formula as an array.
Usually you’ll want to leave the list as a range of its own, but depending on the occasion, it could be tidier to have it inline.