However, you still need to understand how approximate matches work.
Let’s take a look at how VLOOKUP handles approximate matches in more detail.
That’s why you’re using the approximate match setting to start with.
Then it steps back one row and returns the result from the column you’ve specified.
What if the lookup value is greater than the top value in the table?
In that case, VLOOKUP will return a value from the last row in the table.
This is what we see with Tanaka.
Finally, what happens if the lookup value is less than the first value in the table?
In that case, you’ll see the #N/A error.
For example, if I change Bueller’s sales figure to $49,000, VLOOKUP returns NA.
To handle this situation, you could useIFERRORto catch the NA error and return a commission rate.
However, a better solution is just to add another row to the table to handle lower sales numbers.
That’s because when the new row is inserted it pushes down the named range.
So, instead, I’ll insert a new second row and fill down values from the first row.
Then I’ll change the values in the first row so that it handles all commissions down to zero.
Now the named range includes all rows in the table, andVLOOKUPcorrectly calculates a commission rate for Bueller.
