6

There is a table of values in sheet 1 which I want to conditionally format(Highlight the cells in color if the nested value finds a match in the table of sheet 2).

But it seems like vlookup function couldn't be used in combination with conditional formatting.

When I put in the formula for format rules(I choose "Custom formula is"),it doesn't work.

Here is the formula I use.

Custom formula is "B1=vlookup(B1,Sheet2!$A$1:$A$6,1,false)"

Is there anything wrong here in this step? Can Vlookup function be used with conditional formatting?

A range of cells which I want to conditionally format

A range of cells which I want to conditionally format

A column of values for looking up

A column of values for looking up

Vlookup function used with indirect function

Vlookup function used with indirect function

Kenny Horna
  • 13,485
  • 4
  • 44
  • 71
John Huang
  • 308
  • 1
  • 3
  • 11

1 Answers1

16

There is nothing basically wrong with the formula, except that when the lookup list is in another sheet, you have to use Indirect like this:

=B2=vlookup(B2,indirect("Sheet2!$A$1:$A$6"),1,0)

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • I also changed this formula into vlookup(B2,indirect("Sheet2!$A$1:$A$6"),1,0) but it still didn't work. Please take a look at the latest pic which I just uploaded above. – John Huang Feb 22 '21 at 11:10
  • 1
    Sorry, you had a hash in front of your numbers so they are text, not numbers. So your original formula but with indirect is the one you want =B2=vlookup(B2,indirect("Sheet2!$A$1:$A$6"),1,0) – Tom Sharpe Feb 22 '21 at 11:34
  • I have the same problem, I create a formula to check if cell value is bigger than 60, like: VLOOKUP(J5, INDIRECT('data validation'!$L$2:$N$9), 3, TRUE)>60 but I don't get any formatting. if I put = at the beginning it gives me error. – GurhanCagin Mar 15 '22 at 11:41