I have some cells which I would like to format to display the level achieved for each person: enter image description here

On the right I have a table called Table3 which contains the data of the level achieved by each person. This is shown on the left by a bar graph.

The formula I have in B2 to give me the TRUE and FALSEs for the conditional formatting is: =VLOOKUP($A2,Table3,2,FALSE)>=B$1. However, if copy and paste this formula into the conditional formatting dialogue box I get an error saying the formula is not valid. If I however replace Table3 with $G$2:$H$5 it works perfectly.

So, why does conditional formatting not like my table names, and is there a way to use tables when doing conditional formatting?

  • Do you have any hiding rows or filter in Table3? if it is normal no filter no hidden rows it worked with me – yass Jul 10 '17 at 19:43
  • I don't have anything hidden or filtered in by workbook – M.Hesse Jul 10 '17 at 19:57
  • I just looked at this again and it appears to be problem with using table names, not named ranges. I have amended by question to specifically ask about table ranges. – M.Hesse Jul 10 '17 at 20:04
  • If Table3 is not a named range then you have to write sheet!$G$2:$H$5 in Vlookup you cannot just write the name of the sheet only – yass Jul 10 '17 at 20:10
  • use named range instead of sheet name in conditional Formatting – yass Jul 10 '17 at 20:30

To reference a table within conditional formatting formula you will need to use INDIRECT("<Table_Name>").

Your formula will thus be : =VLOOKUP($A2,INDIRECT("Table3"),2,FALSE)>=B$1

I don't know why but it just works.

Reference: How to use a table name in data validation lists and conditional formatting formulas

(BTW: Why don't you just use the "Data Bar" Conditional Formatting in your table? It would be much easier.)

Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.