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:
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 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:
Then we use the LOOKUP function again with height for lookup value and and all heights in the table as the result vector.
The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...
The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range. LOOKUP's default behavior makes it useful for solving certain problems...
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX...
The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...
The OR function is a logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE. For example, to test A1 for either "x" or "y", use =OR(A1="x",A1="y"). The OR function can be used as the logical test inside the...