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 the 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.
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: