I have a Google sheet table which likes this named ‘Sheet 2’
| Phone | CNo | ||
|---|---|---|---|
| xxx | a | b | c |
| yyy | d | e | f |
another table like this named ‘Phone 1’
| CNo | Year |
|---|---|
| a | 1753-01-01 |
| b | 1753-01-01 |
| c | 2019-09-13 |
| d | 2020-12-11 |
| e | 2024-09-22 |
| f | 2016-04-22 |
I want to check if the Year of the CNo is > 2019, if yes, the box will show green, if no remains unchanged.
However, when I tried to use this formula in the conditional formatting it returns an error. What should I do?
=IFERROR(Year(Vlookup(E1,'Phone 1'!$B$2:$C$26036,2,false))>=2019,false)
Conditional formatting rules cannot refer to other sheets directly. Use indirect(), like this:
=vlookup(E1, indirect("Phone 1!B2:C"), 2, false) >= 2019
The formula assumes that column 'Phone 1'!C2:C contains numbers like 2019. If the column contains dates like 2019-01-01 instead, use this:
=year(vlookup(E1, indirect("Phone 1!B2:C"), 2, false)) >= 2019
There is usually no need to mask errors in a conditional formatting rule. Errors get the same result as false — no formatting gets applied.