Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles CONDITIONAL FORMATTING How to Highlight Approximate Match Lookup Using Conditional Formatting

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:
Solution examples
We are trying to create a pivot table and combine two separate workbooks, that do not have the same data in the same cells. Is this even possible? For example the data is sales on items by month, one workbook has the sales broke down by month, the other work book has sales broke down by week. We can convert the data, before hand, but we are trying to forecast sales of times.
Solved by T. L. in 60 mins
I have two lists. One is a list of part numbers. The other is a list of long text descriptions. I can use VLOOKUP with "*"&cell address"*" to find the first match. But how can I find the second and third matches? I have tried using INDEX and SHORT but the wild cards no longer find the match like they do in VLOOKUP. Can you help?
Solved by S. J. in 36 mins
I need a formula that will check if 2 or more cells containing names match then add the adjacent cells to matching name together.
Solved by F. D. in 59 mins
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
Hello, I am working with two workbooks. I am trying to get specific information from one onto the other using 3 criteria. I currently using a index match formula. Both sheets have the same information but one has a response that I am trying to match and populate onto the new report. However, instead of looking up the actual value of the cell - its matching the location and giving me whats in that cell. =INDEX('[Arrow OOR - 9-11-18.xlsx]Response'!$A:$L,MATCH(1,(A:A=A4)*(B:B=B4)*(F:F=F4),0),12)
Solved by I. C. in 52 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc