Conditional formatting using formula "Vlookup" in google sheet

Question

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 column of values for looking up

Vlookup function used with indirect function

Selwin

share a copy of your sheet & formula – Anthurium

Answer 1

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)

Aracelyaraceous

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. – Selwin

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) – Aracelyaraceous

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. – Haddad