This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add more rows to the Excel tables, the conditional formatting expands automatically.
The Excel user enters a value in cell C21 and the conditional formatting formulas applied to all three Excel Tables highlights values based on the relationship between tables and the search value.
The search value in cell C21 is found in B7, the corresponding value on the same row in Table1 is in cell C7. That value is used to do another lookup in column Category Table2 and four records are found.
Table2 has values in column Company in common with Table3 column Company, this relationship is used to highlight the corresponding values in column "Sales persons".
What is a relational table?
In a relational database (Microsoft Access), the data in one table is related to the data in other tables. In general, tables can be related in one of three different ways: one-to-one, one-to-many or many-to-many. The relationship is used to cross-reference information between tables.
Source: University of Sussex
How to apply Conditional Formatting?
Select the first Excel Table (cell range B5:C19).
Go to tab "Home" on the ribbon.
Click the "Conditional Formatting" button.
Click "New Rule..." and a dialog box appears.
Click "Use a formula to determine which cells to format".
Type the formula below in "Format values where this formula is true:"
Click "Format" button.
Go to tab "Fill.
Pick a color.
Click OK button.
Click OK button.
Repeat steps with the remaining Excel Tables and the corresponding CF formulas described below.
Conditional formatting formula applied to Excel Table Table1
This allows Excel to compare each value in column Item with the value specified in cell $C$21. This cell reference is an absolute cell reference which you can tell by the dollar characters ($). An absolute cell reference is locked to a specific cell and does not change when the Conditional Formatting formula moves to the next cell.
We do want it to always compare the value in cell C21 with all values in column Item, no matter what.
Conditional formatting formula applied to Excel Table Table2