A few months ago, I wrote about using Excel to sort a table of names that had been created in Word. And last year, I mentioned a dynamic report I’d created with a colleague, stringing together a few formulae, some data validation and some conditionally formatted cells. Well, today, that same colleague came to me and asked to borrow my Excel skills again.
He had a list of potential sales opportunities and wanted to highlight any cells in the column of client names that matched a list of primary accounts on another sheet. It sounded “do-able”, with some conditional formatting and a formula.
I like a challenge – and it’s as close to any development work as I get these days – so I got stuck in.
The actual formula used in my conditional formatting rule (applied to data in column E) was this:
=MATCH($E2,'Primary Account List'!$A$4:$A$34,0)
Basically, it’s saying, for the value in this cell, have a look at the data in the Primary Account List sheet, cells A4-A34 and if there’s a match, apply the formatting (bold, orange). I did put $E1 in at first, and the lookup was one cell out… (row 1 is the column headers). E2 is the start of my list but the same formula is used in the conditional formatting rule that covers all the cells.
Featured image: author’s own.