Conditional formatting mystery https://onedrive.live.com/redir?resid=D62CBB3CF36

Question

Conditional formatting mystery

https://onedrive.live.com/redir?resid=D62CBB3CF367F163%21166

In this sheet (squares tab), I have divided up the UK into OS gridsquares. Basically, a small square should turn yellow if there is anything in Column D corresponding to that square.

Column B has all the squares. Column D has callsigns contacted in those squares. The "map" over to the right is arranged in squares. I have set up conditional formatting to turn the cell background yellow if there is anything in column D.

This works all the way through the map - except for large square J (Northern Ireland). This is cells B139 to B193 in the lookup vector, and cells T79 to AC88 on the map display and where the conditional formatting SHOULD take effect.

I just can't fathom this one out. I know SOME conditional formatting is working in those cells because the background has turned turquoise if the cell is empty (that's the first condition). I've checked using formulas that the logic is seeing the cells as equal value (and it is), and that cell formats are the same etc.

Can anyone solve this one?

Thanks !!

Explanation / Answer

Hi..

LOOKUP is not the right function for this. Use VLOOKUP instead:

=NOT(VLOOKUP(G5,$B$5:$D$3141,3,FALSE)=0) :

VLOOKUP with FALSE as last argument looks for an exact match, as opposed to LOOKUP which looks for an approximate match.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.