a product code or customer ID).
This unique identifier must exist in the database, otherwise VLOOKUP returns us an error.
In certain circumstances, this is exactly what we need.
Otherwise their commission is only 20%.
This formula makes use of the Excel function calledIF.
“, or, put another way, “Are the total sales less than the threshold”.
Let’s make it more complex.
This is called a nested function (a function within a function).
It’s perfectly valid in Excel (it even works!
), but it’s harder to read and understand.
This is a tutorial on VLOOKUP, not on Excel in general.
Anyway, it gets worse!
Now the formula in cell B2, while correct, has become virtually unreadable.
No-one should have to write formulae where the functions are nested four levels deep!
Surely there must be a simpler way?
VLOOKUP to the rescue!
Let’s redesign the worksheet a bit.
They may have created sales of $34,988.
It’s important to note that $34,988 does not appear in the rate table.
In this example, it’s the rate table, of course.
Important: It is the use of this field that differentiates the two ways of using VLOOKUP.
When using VLOOKUP, it is vital that you make the correct choice for this final parameter.
To be explicit, we will enter a value oftruein theRange_lookupfield.
In other words, we’re looking for an exact match.
But in this other use of VLOOKUP, we are not necessarily looking for an exact match.
In this case, “near enough is good enough”.
But what do we mean by “near enough”?
Why did it choose the row in the table containing 30% ?
What, in fact, does “near enough” mean in this case?
