-: Now before we move on to index and match
and offset functions, there's one other topic related
to lookup that I'd like to quickly address.
And that's the final argument
in the formula called range lookup, where you determine
either true for approximate match or false for exact match.
Now, I mentioned that 99%
of the time you'll use exact match there as it sounds.
That's exactly what you use when you wanna find
the exact version of your lookup in your table array.
That's very common when you're trying to match key columns
or text fields.
But depending on the type of work that you do
there are some very useful
and common use cases where approximate
or fuzzy match look ups may be actually more appropriate.
So what approximate or fuzzy match does is it
allows you to determine where a specific lookup value falls
within a range of values.
So instead of just trying to find an exact match
this will tell Excel to find the closest match that's equal
to or less than your lookup value.
Now to show you exactly how that works
what I've done here is create some data
with some order IDs and order amounts.
And this lives in a tab called fuzzy match lookup
which I've recently added.
If you don't see that tab
go ahead to your course dashboard
download a fresh copy of that section five Excel workbook
and you should see that populate.
But basically what we have here is a sample
of about 25 orders.
We've got random IDs here in column A
and we have the order amount in column B.
Now the key is this little lookup table that I've created
in sales, E one through G five.
And this is basically a discount schedule.
It tells us that for orders between zero and 24.99
there should be no discount applied.
But if you spend more than $25, you get 5% off.
If you spend more than 50, you get 10% off.
And if you spend more than a hundred, you'll get 20% off.
And this is a pretty common realistic structure that a lot
of retail companies use in the real world.
And our goal here is to pull in the proper discount right
here in column C based on our order amounts in column B.
And we know that a lookup function is gonna be helpful here
because we're trying to retrieve related values
from another table.
But the problem is if we try to type a traditional V lookup
and say, let's find the order amount within our table
array here, note that column E, the order amount
these are just labels for my own purposes.
What I'm looking to do is actually look up from column F
and return the value in column G,
which contains my discounts.
So that's my table array.
I'm gonna press F four to lock that in and I want the value
from the second column over.
Now here's that last argument that range lookup.
If we stick with the norm and type false
or a zero here for exact match and close it off
take a look at what happens when we apply it down.
Most rows return an NA
because we're not finding those exact order amounts
in our lookup table.
In fact, the only values that don't return an error
are the cases where an order happened to be exactly the same
as one of our minimums here in column F.
So this is a perfect opportunity to use that fuzzy match
or approximate match option
by simply changing that last argument from a zero
to a one or a true and pressing enter
and check it out.
When we apply this down
now we're getting the exact values we want 82, 68 returns
a 10% because remember how this works?
We're looking up the closest match that's
either equal to or less than the lookup value
so equal to or less than $82.68.
As we kind of scroll through column F
$50 is the last value that meets those criteria.
And we grab the value
from the second column over 10% and pull it back in.
And as you can see, 4 95 should be no discount.
93, 90 eights, 10% over a hundred dollars is 20%.
So this is working exactly as we'd expect.
Now, one thing to note here
make sure that you're defining your groups based
on the minimum value in that group.
That's why I added the label here, just for our own purposes
and the actual minimums that the VLOOKUP will work off.
And not only that, but once you've defined those minimums
make sure that your lookup table is in ascending order.
because remember that lookup formula is gonna start
at the top and work its way down
until it finds the most appropriate match.
So there you have it.
Good real world scenario of why you might want to
use fuzzy match or approximate match instead
of exact match in a VLOOKUP function.