Highlight lookups in relational tables
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: onetoone, onetomany or manytomany. The relationship is used to crossreference 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
You need to use the INDIRECT function to be able to reference an Excel defined Table in a Conditional Formatting formula. Table1[@Item] is a "structured reference" which is a way to reference Excel Tables, the @ character tells Excel to use only the cell in the same row as the Conditional Formatting formula in column Item.
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
Explaining Conditional Formatting formula in Table2
The steps below demonstrate what is calculated by the Conditional Formatting formula in cell E5.
Step 1  Find matching items
The MATCH function returns the relative position of the matching value in column Item Table1.
MATCH($C$21, INDIRECT("Table1[Item]"), 0)
becomes
MATCH("Jig saw", {"Drill driver"; "Angle grinder"; "Jig saw"; "Gas frame nailer"; "Table saw"; "Router"; "Mitre saw"; "Jointing machine"; "Random orbit sander"; "Planer"; "Combi drill"; "Impact wrench"; "Circular saw"; "Rotary hammer drill"; "Biscuit joiner"}, 0)
and returns 3. "Jig saw" is on the third row in column Item Table1.
Step 2  Return the value on the same row from column Category
The INDEX function returns a value from column Category based on the relative row number returned from the MATCH function.
INDEX(INDIRECT("Table1[Category]"), MATCH($C$21, INDIRECT("Table1[Item]"), 0))
becomes
INDEX(INDIRECT("Table1[Category]"), 3)
becomes
INDEX({"A";"B";"C";"D";"A";"B";"C";"D";"A";"B";"C";"D";"A";"B";"C"}, 3)
and returns "C".
Step 3  Compare value in column Category Table 1 with Category Table2
INDEX(INDIRECT("Table1[Category]"), MATCH($C$21, INDIRECT("Table1[Item]"), 0))=INDIRECT("Table2[@Category]")
becomes
"C"=INDIRECT("Table2[@Category]")
becomes
"C"="A"
returns FALSE. Cell E5 in Table2 is not highlgihted.
Conditional formatting formula applied to Excel Table Table3
The animated image above shows what happens when different search conditions are used.
You can find the array formula in cell C23 here:
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a dropdown list and Conditional Formatting. There are [โฆ]
How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD [โฆ]
How to change cell formatting using a Drop Down list
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column [โฆ]
Prevent duplicate records [Data Validation]
Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share [โฆ]
Working with three relational tables
I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers [โฆ]
Extract unique distinct values from a relational table
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, [โฆ]
Merge two relational data sets
This article demonstrates how to merge two relational data sets before creating a Pivot table. A Pivot Table is limited [โฆ]
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can [โฆ]
One Response to โHighlight lookups in relational tablesโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
[โฆ] Applying conditional formatting to related tables [โฆ]