Go Back

How to Highlight Approximate Match Lookup Using Conditional Formatting

If you need to highlight rows and columns with an approximate match in Excel, then this article will help you in developing an understanding of this. Therefore, the main purpose of this article is to explain how to highlight the match lookup using the conditional formatting tool. 

Highlight approximate match lookup using conditional formatting

Highlighting the approximate match lookup using conditional formatting option, also the combination of LOOKUP function and OR function will solve the problem. 

Hence, the following formula is generic and will highlight the approximate match lookup values accordingly.

Formula using OR and LOOKUP functions

=OR($Xn=LOOKUP(value1,data1),X$n=LOOKUP(value2,data2))

Explanation of formula

The LOOKUP function can search for a specific value in the array or data. In the above formula, Xn refers to any cell under consideration in the Excel sheet.

In the formula described above, value1 and value2 in the cells are using OR function to lookup with defined criteria, and have conditional formatting. Each cell can have different criteria to format and can be adjusted in the setting.

Example 

A two-dimensional array of 6×6 has been considered, which shows row numbers as the parameter width and column number as height. These arrays are named as widths and heights respectively. The entries of the array show the area corresponding to the width and height.

A sample of each, width and height are separate which is then looked for in the 2-D array.

Step 1:

Firstly, let’s have a look at the sample that we will go through in the figure below:

Figure 1. Sample sheet to highlight approximate match lookup conditional formatting

Step 2:

Secondly, in order to calculate the value of the area and making an approximate match, enter the following formula in cell K4:

=INDEX(C4:H9,MATCH(width,widths,1),MATCH(height,heights,1))

INDEX function is basically to retrieve the value of the area corresponding to the entered width and height. MATCH function is used to make an approximate match by setting its 3rd parameter to 1. The two MATCH functions return the row and column number respectively which is used as an address by the INDEX function to retrieve the value in that place in array C4:H9.

Figure 2. Finding the approximate match from the array

Step 3

Thirdly, select the range of cells (here: B3:H9) and adjust the format setting. After that, go to the Conditional Formatting option in the drop-down menu of Home button (in the menu bar) and click the “Manage Rules” option at the end of the menu.

Figure 3. Selecting and Conditional Formatting the cells

Step 4

After that, Conditional Formatting Rules Manager’s pop-up window will appear. Click on the “New Rule” button in this window.

Figure 4. Creating a New Rule from Conditional Formatting Rules Manager

Step 5

Now, another pop-up window will appear to create a new formatting rule of your own. There are several options to create a new rule; select the “Use a formula to determine which cells to format” option from the menu.

As a result, a formula bar will appear in the same pop-up window below those options.

Figure 5. Enter the formula to define which cells to format

Step 6

Let’s enter the formula written below in this formula bar:

=OR($B5=LOOKUP(width,widths),B$5=LOOKUP(height,heights))

Figure 6. Defining to format the approximate match lookup, in conditional formatting

Step 7

After that, click on the format button to specify the format of the cells, which will fulfill the criteria according to the formula. This will open a pop-up window that will let you format the number, font, border and which color should be filled.

Figure 7. Defining the formatting for the cells

Step 8

Furthermore, click the “OK” button which will take you back to the Conditional Formatting Rules Manager showing the formula, format and the range of cells to which it has been applied. Click the “OK” button to close this window.

Figure 8. Conditional Formatting rules defined

Step 9

Next, the cells that approximate match lookup and formatted accordingly.

Figure 9. Result-1 of highlighted approximate match lookup according to conditional formatting criteria

Step 10

Finally, it can be seen that changing the Width and Height in the box on the right affects the formatting region.

Figure 10. Result-2 of highlighted approximate match lookup according to conditional formatting criteria

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

How do I lookup and match approximate dates from 2 tables of unequal size?
Solved by V. C. in 19 mins
conditional formatting to highlight matching data
Solved by Z. H. in 15 mins
v LOOKUP, CONDITIONAL FORMATTING for 2 separate sheet in several columns
Solved by D. S. in 29 mins

Leave a Comment

avatar