Search with conditional formatting
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
- Select cell range A5:D25
- Go to tab "Home"
- Click "Conditional formatting" button
- Click "New Rule.."
- Click "Use formula to determine which cells to format"
- Type conditional formatting formula
- Click "Format..." button
- Select a color
- Click OK
- 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
Related posts:
Highlight duplicates using conditional formatting in excel
Highlight the second or more duplicates in two lists using conditional formatting in excel
Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
Highlight smallest duplicate value in a column using conditional formatting in excel
Recommended categories
Brilliant! add data validation for the cell C2 and C3 and it makes the job even easier.
Cyril,
Thanks!
Here is the same file with data validation lists in cell C2 and C3:
Search-with-conditional-formatting-data-validation.xlsx
pls tell me, if how can i pick data in particular cell instead of highlighting.
e.g. if i want in value of first name or company name or last name in europe region in cell D3. how to do this
gaurav,
Company name
=INDEX($B$6:$D$25;MATCH("Europe", $A$6:$A$25, 0), 1)
First name
=INDEX($B$6:$D$25;MATCH("Europe", $A$6:$A$25, 0), 2)
Last name
=INDEX($B$6:$D$25;MATCH("Europe", $A$6:$A$25, 0), 3)
How to return multiple values using vlookup