How to highlight approximate match lookups

In this video, we'll look at how to highlight approximate match lookups with conditional formatting.

Here we have a simple lookup table that shows material costs for various heights and widths. The formula in K8 uses the INDEX and MATCH functions to retrieve the correct cost based on width and height values entered in K6 and K7.

Note that the lookup is based on an approximate match. Since values are in ascending order, MATCH checks the values until a larger value is reached, and then steps back and returns the previous position.

Let's build a conditional formatting rule to highlight the matched row and column.

As always with more tricky conditional formatting, I recommend you work with dummy formulas first, and then transfer a working formula directly to the conditional formatting rule. This way, you can use all of Excel's tools when you're debugging the formula, which will save you a lot of time.

I'll set up the formula for width first. We need to return TRUE for every cell in row 7, where the matched width is 200.

This means we start our formula with $B5=, and we need to lock the column.

=$B5=

Now, we can't look for 275 in the widths column, because it isn't there. Instead, we need an approximate match that finds 200, just like our lookup formula.

The easiest way is to do this is to use the LOOKUP function. LOOKUP automatically does an approximate match, and, instead of returning a position like MATCH, LOOKUP returns the actual match value. So, we can write:

$B5=LOOKUP($K$6,$B$6:$B$12)

With our input width for lookup value and all of the widths in the table for result vector.

If I use F9, you can see the value LOOKUP returns.

Now when I enter the formula across the table, we get TRUE for every cell in the 200-width row.

Now we need to extend the formula to match the height column. To do this, I'll add the OR function, and then a second formula to match height.

We'll start the formula the same way, but this time we need to lock the row:

=B$5

Then we use the LOOKUP function again with height for lookup value and all heights in the table as the result vector.

=OR($B5=LOOKUP($K$6,$B$6:$B$12),B$5=LOOKUP($K$7,$C$5:$H$5))

When I copy the formula across the table, we get TRUE for every cell in the matched column and every cell in the matched row - just what we need for conditional formatting.

I can just copy the formula in the upper left cell exactly, and create a new rule.

Now if I change the width or height, the highlighting works as expected.

Finally, if you only want to highlight the lookup value itself, it's a simple change. Just edit the formula and replace the OR function with the AND function.

=AND($B5=LOOKUP($K$6,$B$6:$B$12),B$5=LOOKUP($K$7,$C$5:$H$5))