Highlight Intersecting Value in Google Sheets in a Two Way Lookup

Published on

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.

Highlight Intersecting Value in Google Sheets

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.

Two-way lookup and then highlight Intersecting Value

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.

Highlight Intersecting Value in Sheets - formula explained

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.

Intersecting Value in Sheets - how to apply conditional formatting

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)

Intersecting Value in Sheets - All about the formula

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.

Find the cell address of the Intersecting Value in Google Sheets

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!

Get My Demo Sheet

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.