How to highlight approximate match lookups

Here we have a simple lookup table that shows material costs for various heights and widths.

Note that the lookup is based on an approximate match.

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

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.

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.

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

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.

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.