How do I conditionally format each individual cell in B27:AM46 based on the table in A94:B114?
Copy of Sheet: https://docs.google.com/spreadsheets/d/1oEOcDbQW-QRhjHDY1D04R9XNStyeTKA44GEaPrlh3sk/edit
For example, ARS is 3. Every cell that contains ARS in B27:AM46 would be red.
1 - Click Format and then Conditional Formatting.
2 - Under the "Format cells if" drop-down menu, click Custom formula is.
3 - Add new rule and then Custom formula is.
Click Value or formula and add the formula and rules. for each formula in the following table in step 02
Paste this formula for each rule
| Rules | Formula |
|---|---|
| Red | =IFNA(VLOOKUP(REGEXEXTRACT(B2,"(.+) "),$K$2:$L,2,0),"")=1 |
| white | =IFNA(VLOOKUP(REGEXEXTRACT(B2,"(.+) "),$K$2:$L,2,0),"")=2 |
| red | =IFNA(VLOOKUP(REGEXEXTRACT(B2,"(.+) "),$K$2:$L,2,0),"")=3 |
The lookup table needs to be on the same sheet.
