Explanation for a VLOOKUP formula used in a Conditional Formatting rule

I have the following VLOOKUP formula for a Conditional Formatting rule:

=VLOOKUP(A1,Sheet1!$A$2:$C$17,3,FALSE)<>0

This works fine and does what I want, which is this: If column C (3rd column) in the lookup table contains any value (i.e. <>0), apply some formatting to the current cell.

What I don't understand is the A1 value used as the first argument in the formula. When I entered the above rule using Conditional Formatting -> New rule -> Use a formula to determine which cells to format, I specified the entire worksheet of Sheet2. So when I enter a value in any cell in Sheet2, the formula is applied and really works beautifully.

But why does it work when the value to look up is specified as A1?

I am using Excel 2016 on Windows 7 x86.

2 Answers

He means that if you go to some other cell in the spreadsheet, C43 say, AND then check the conditional formatting rule to see what the formula is shows you is, that you will see that cell's address (C43) in place of the A1 you see when you are on cell A1.

This is because of relative referencing, a very common and exceedingly useful (if you know it is happening!) feature of Excel.

If you want the rule to ALWAYS refer to cell A1 no matter what, highlight cell A1, then edit your conditional format to use the formula but with $A$1 instead of A1.

As it is now, the lookup value your conditional formatting is using is ALWAYS the value in whatever cell is highlighted which may... or may not... work out for you. It might work in the early usage and begin to fail as data piles up or other things become more complex. It might never have worked when the highlight was not on A1 and you didn't notice... you simply never had a reason to move the cell hightlight to some other cell.

Look up relative referencing on the internet. It's very much worth learning how to use it. And how to recognize it is happening when you did not intend to use it. (Don't let that last bit chase you off... things often work just fine even when you didn't intend to use it. It's just that, well, it was kind of luck and luck is not something to depend upon...) Some hate it by the way, some, like me, find it exceedingly useful. I bet there's a few in between too.

Conditional formatting formula is always applied at cell level.

If you go to another cell in the range with the conditional formatting, you'll probably see different references.