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.

It seems the function I needed was =MATCH() although now I’m wondering if =VLOOKUP() might have been more appropriate.

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.