Highlight lookup values
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and rows.
I am also going to explain how to create the highlighting and the conditional formatting formulas behind.
Setting up Conditional Formatting
- Select cell range A5:D25.
- Go to tab "Home".
- Press with left mouse button on the "Conditional formatting" button.
- Press with left mouse button on "New Rule..".
- Press with left mouse button on "Use a formula to determine which cells to format".
- Type conditional formatting formula.
- Press with left mouse button on "Format..." button.
- Select a color.
- Press with left mouse button on OK.
- Press with left mouse button on OK.
Repeat the 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 when it comes to conditional formatting. Remember that cell range A5:D25 was selected before you created the conditional formatting rules, that will be the range the conditional formatting is applied to.
The first conditional formatting formula is:
There are two logical expressions in this formula
- (A$5=$C$2)
- ($A5=$C$3)
They return TRUE if the condition is met or FALSE if not. The asterisk multiplies the boolean values and that will create AND logic between these two logical expressions.
This means that if both expressions return TRUE then the formula returns TRUE or the equivalent numerical value. TRUE is 1 and FALSE is 0 (zero).
Expression 1 Expression 2
TRUE * TRUE = TRUE
FALSE * TRUE = FALSE
TRUE * FALSE = FALSE
FALSE * FALSE = FALSE
The parentheses determine the order of calculation, we want to evaluate the equal signs first and then multiply the expressions.
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 the row part has a $ indicating it is locked. This makes the formula compare column headers only.
Example, in cell B7, the cell reference changes to:
(B$5=$C$2)
and becomes
"Company Name"="First Name"
and returns FALSE. Column B is not highlighted.
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.
Cf search category
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
The picture above shows you how to highlight rows containing text strings using conditional formatting. Example, continents criterion (cell B3) […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
Conditional formatting category
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Conditional Formatting categories
Excel categories
4 Responses to “Highlight lookup values”
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.
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