< Go Back

Highlight approximate match lookup conditional formatting

If you need to highlight rows and columns with an approximate match, 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. 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

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

Explanation

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.

How the Formula to Highlight Approximate Match Lookup Conditional Formatting Works

In the formula described above, value1 and value2 in the cells are using OR function to lookup with defined criteria, and have conditionally 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

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar