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".
- Click the "Conditional formatting" button.
- Click "New Rule..".
-
Click "Use a formula to determine which cells to format".
- Type conditional formatting formula.
- Click "Format..." button.
- Select a color.
- Click OK.
- Click 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.
Related articles
Highlight records โ multiple criteria [OR logic]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in [โฆ]
Highlight records containing text strings (AND Logic)
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. [โฆ]
Highlight overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that [โฆ]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it [โฆ]
Highlight dates in a date range
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 [โฆ]
This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($B$3:$B$15, [โฆ]
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.
English
Spanish
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