Are you struggling to highlight intersecting values in Google Sheets using a two-way lookup? Unfortunately, there is no built-in conditional formatting rule to accomplish this task. But fear not! In this tutorial, I will guide you through a combination of formulas that will allow you to achieve this desired effect.
Why Conditional Formatting?
Conditional Formatting is a powerful tool that can enhance your data in Google Sheets. While there are built-in formatting rules available, sometimes you may need to use custom formula rules to achieve the desired outcome.
Understanding Intersecting Values in Two-Way Lookup
Before we dive into the formula, let’s first understand what intersecting values are in a two-way lookup. Take a look at the image below to get a visual representation of what we mean by intersecting values.
In a previous tutorial, I shared how to perform a two-way lookup in Google Sheets using Vlookup formulas. However, these formulas cannot be directly used in conditional formatting. That’s where our unique approach comes into play.
The Formula to Highlight Intersecting Values
I will now introduce you to a formula that will highlight intersecting values in a two-way lookup. Let’s start with Example Formula #1:
=ADDRESS(ROW(),COLUMN(),4)=CHAR(64+MATCH($G$2,$A$1:$1,0))&MATCH($G$1,$A$1:$A,0)Allow me to guide you through the process of applying and adjusting this formula for your specific data range. I will also explain the role of each function and how this formula works.
But before we proceed, let’s see how the highlighting of intersecting values takes place.
In this formula, we make use of several functions, such as Address, Row, Char, Column, and Match. Don’t worry, I will explain the purpose of each function and how they contribute to finding the intersecting values in a two-way lookup.
Modifying the Formula for Your Data Range
If your data range is different from mine, you’ll need to make some adjustments to the formula. Let’s say your data is in the range B3:F, here’s how you can modify the formula:
Example Formula #2:
=ADDRESS(ROW(),COLUMN(),4)=CHAR(64+MATCH($G$2,$A$3:$3,0))&MATCH($G$1,$B$1:$B,0)Instead of A1:1, you should use A3:3 for the first row to lookup. Similarly, instead of A1:A, you should use B1:B for the first column to lookup. Remember to adjust these references according to your specific data range.
Setting the Custom Formula Rule
Now, let me show you how to set this formula as the custom formula in conditional formatting.
- Select the range of data you want to apply the conditional formatting to.
- Go to the Format menu and choose Conditional formatting.
- In the Apply to Range field, enter the range you selected in step 1.
- In the Custom formula is field, paste the formula we discussed earlier.
- Choose the desired cell color for highlighting the intersecting values.
- Click Done.
And that’s it! You’ve successfully set up the custom formula rule to highlight intersecting values in Google Sheets using a two-way lookup.
Formula Explanation
Now, let’s delve into the explanation of the formula we’ve been using (we’ll focus on Example Formula #1).
The master formula consists of two main parts. The first part returns the current cell address, while the second part returns the cell address of the intersecting value. The equal sign separates these two parts.
The formula works as follows:
=current cell address = cell address of the intersecting valueIn other words, if the current cell address matches the cell address of the intersecting value, the cell will be highlighted.
Google Sheets requires some kind of test to highlight a cell, which is why we have two parts to our formula.
The second part of the formula determines the cell address of the intersecting value. However, this alone is not sufficient for conditional formatting. This is where the relevance of the first part of the formula comes into play.
The second part of the formula uses two Match functions to determine the column and row numbers of the search keys, respectively. These numbers are then converted into column headings using the Char function.
For example, if the column number is 4, the Char function will return “D”. When these values are combined, we get the cell address of the intersecting value.
If you want to extract the value itself, rather than the cell address, you can use the Indirect function with this formula. Consider this as an additional tip.
=INDIRECT(CHAR(64+MATCH($G$2,$A$1:$1,0))&MATCH($G$1,$A$1:$A,0))And there you have it! By following the instructions provided, you can now highlight intersecting values in Google Sheets using a two-way lookup.
If you have any questions about applying this formula or need further assistance, feel free to ask. Enjoy exploring the possibilities of Google Sheets!
Check out Crawlan.com for more exciting tutorials and tips!
