In order to highlight intersecting value in Google Sheets, that in a two-way lookup, there is no built-in conditional formatting rule. We must depend on a combination of formulas.
Conditional Formatting is always best to enhance your data in Google Doc Spreadsheet. There are built-in formatting rules. But most of the cases, we may want to use custom formula rules.
What is intersecting value in a two-way lookup? How to highlight this value in Google Spreadsheet?
Intersecting Value in Two Way Lookup:
See the below image to understand what’s the so-called intersecting value in a two-way lookup.
In one of my earlier Google Sheets tutorial, I have shared how to perform a two-way (horizontal and vertical) lookup.
There I have provided two different types of formulas to extract intersecting values – How to Perform Two-way Lookup Using Vlookup in Google Sheets.
Both of the formulas are Vlookup oriented. But as you may know, a custom formula in Conditional formatting may not support a regular type formula.
To highlight intersecting value in Google Sheets that in a two-way lookup, you can’t use the same formula that extracts the value.
Here I am using an entirely different approach. Here is a real-life example.
The Formula to Highlight Intersecting Value in Google Sheets in a Two Way Lookup
Example Formula # 1:
=ADDRESS(ROW(),COLUMN(),4)=CHAR(64+MATCH($G$2,$A$1:$1,0))&MATCH($G$1,$A$1:$A,0)
I’ll guide you how to apply and adjust this intersecting value conditional formatting formula for any data range. Also, I’ll do my best to make you familiar with this formula.
Before that see how the highlighting of intersecting value in a two-way lookup taking place.
In this combination, you can see the functions Address, Row, Char, Column, and Match in use.
I will surely explain the role of each function and how this formula finds intersecting value in a two-way lookup.
Before that, better you must know how to apply this formula in your data range, which may be different from mine.
How to Modify The Formula That Highlights Intersecting Value in Two-way Lookup
I have my data in the range A1: D5. But my formula covers the range A1:1, I mean an unlimited number of columns and A1: A an unlimited number of rows.
I have my search keys for the horizontal lookup in cell G2 and vertical lookup in cell G1. I have created actually a drop-down for switching the key easily.
You can just type the search key or create a similar drop-down menu using the Data > Data validation feature.
As you can see my data range is A1: D. So the first row to lookup using the search key would be column A1:1 and the first column to lookup would be A1: A.
You only need to change this row and column references in the formula if your data range is different.
Suppose if your data is in the range B3: F, you should change the formula as below.
Example Formula # 2:
=ADDRESS(ROW(),COLUMN(),4)=CHAR(64+MATCH($G$2,$A$3:$3,0))&MATCH($G$1,$B$1:$B,0)
First Row to Lookup: A3: 3 (not B3: 3) – use the entire row.
First Column to Lookup: B1: B (not B3: B) – use the entire column.
Now let me show you how to set this as the custom formula in conditional formatting.
Set Custom Formula Rule to Highlight Intersecting Value in Google Sheets
Conditional formatting rule is available under the menu Format.
Apply to Range: For my first example the data range is A1: D. In the second example it’s B3: F.
You can select the entire Spreadsheet. But it may slow down the performance of your Sheet.
Paste my formula in the field “Custom formula is” and set the cell color. That’s all that you want to do to highlight intersecting value in Google Sheets in a two-way lookup.
Formula Explanation
The formula performs as below (I am taking the example formula # 1 for detailing)
The master formula contains two main parts. The first formula part returns the current cell address and the second formula part returns the cell address of the intersecting value. The equal sign separates each formula.
The formula is working like this.
=current cell address = the cell address of the intersecting value
In other words, if current cell address is the cell address of the intersecting value, highlight the cell.
Google Sheets conditional formatting required some kind of test to highlight a cell. That is why the formula has two parts.
The second part of the formula already finds the cell address of the intersecting value. But that is not enough for conditional formatting. There comes the relevance of the first formula part.
How the second part of the formula returns the cell address of the intersecting value?
It’s simple. The first Match formula returns the column number (not relative position) of the search key. That’s # 4 (see the screenshot below).
The fourth column means column D. I have used the CHAR function to convert this number to column heading (D).
=char(64+4)
If it’s 64+5, the column heading letter would be E.
The second Match formula returns the row number (not relative position) of the search key. That’s again #4.
You May Also Like: How to Autofill Alphabets in Google Sheets.
When you combine these values, you will get D4, the cell address of the intersecting value.
If you want you can convert this formula to a Vlookup + Hlookup combination formula that does the two-way lookup!
Just use the Indirect function with this formula to extract the value, not the cell address. This is an additional tip.
=indirect(CHAR(64+MATCH($G$2,$A$1:$1,0))&MATCH($G$1,$A$1:$A,0))
That’s all. Follow the above instructions carefully to highlight intersecting value in Google Sheets that in a two-way lookup.
Feel free to ask me any questions regarding the application of the above formula. Please stick to the topic in your questions. Enjoy!
V E R Y clever! And really useful. Thanks!