## 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: 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*

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 drop-down 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 in a worksheet

This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]

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, […]

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 […]

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 […]