I would like to show you how to search a table using conditional formatting. The criteria highlight matching column and rows.

I am going to explain how to create the highlighting and the conditional formatting formulas behind.

Setting up the conditional formatting

1. Select cell range A5:D25
2. Go to tab "Home"
3. Click "Conditional formatting" button
4. Click "New Rule.."
5. Click "Use formula to determine which cells to format"
   
6. Type conditional formatting formula
7. Click "Format..." button
8. Select a color
9. Click OK
10. Click OK

Repeat above steps and create new conditional formatting rules with these formulas and colors:

and

Explaining conditional formatting formulas

It is important to understand how relative and absolute cell references work. Remember that cell range A5:D25 was selected before you created the conditional formatting rules.

The first conditional formatting formula:

Step 1 - Highlight cells that have a column header equal to the value in $C$2.

(A$5=$C$2)

The cell reference A$5 changes in each cell in cell range A5:D25 but remember only the column reference changes. This makes the formula comparing only the corresponding column header.

Example,

In cell B7, the cell reference changes to:

(B$5=$C$2)

becomes

"Company Name"="First Name"

and returns FALSE.

Step 2 - Highlight cells that have a Region value equal to the value in $C$3

($A5=$C$3)

The cell reference $A5 changes in each cell in cell range A5:D25, only the row reference changes.

Example,

In cell B7, the cell reference changes to:

($A7=$C$3)

becomes

"Europe"="Africa"

and returns FALSE.

Step 3 - Check if both criteria are TRUE

=(A$5=$C$2)*($A5=$C$3)

becomes

=FALSE*FALSE

and returns 0. Cell B7 is not highlighted with the selected color.

Recommended links

You can do a lot of interesting stuff with conditional formatting. You can search for cell values containing text strings or highlight records in a list. You can find many more examples in the conditional formatting category.

Download excel 2007 *.xlsx file

Search with conditional formatting.xlsx