Tip:
Highlight text to annotate it
X
The ability to lookup information in Excel is generally performed by using one of the
VLOOKUP HLOOKUP or LOOKUP functions.
What people don’t realize is the risks that they take depending on how they use those
functions.
In this example what you’ve got here is a table with a number of reference alpha numeric
cells. For ease of comparison I’ve made the information to be looked up exactly the
same. So if I want to see the answer for A2 it must bring back A2, if i want to see the
answer for A21 it must bring back the A21.
Over here you have the cell which specifies what number I am looking for so I am looking
for the answer to A201 which should be A201.
Here’s the Vlookup function and lets just have a quick look into it
by pushing the Function Wizard, what you’re saying is, its asking for the
Lookup value, so you’re saying look up the number A21,
where must I look it up, in this table here and what must it pull through when it finds
it and that’s the second column However this is a vital vital entry, by either
putting in true or false here, you can tell Excel whether it must find an exact match,
so it must find A201, or an approximate match,
so if you put in false you’d find an exact match
if you put in true it’ll find an approximate match.
However for whatever reason Excel has also said that if you omit it totally, it is going
to find an approximate match and that is where one of the problems comes in.
What you have here is that you’ve asked for A201 and yet it is pulling thru A10, now
that same situation happens if you include the criteria true at the end, it is only sorted
out when you include false, and that is because you’ve told Excel look for A201, but only
find an exact match.
So it goes down here and finds and exact match, if there was not an exact match it would give
you an error message, Now the reason that this happens in Excel,
and this error can occur, because the way Excel sorts alpha numeric keys is slightly
different to the way a human would.
So for example I would be happy to sort this list as A1, A2 then A10, A21, A201. However
as far as Excel is concerned, A10 follows A1, A2 is bigger than A10. and hence A201
and A21 is actually bigger than those.
Now the way this would happen is that often someone would enter a part numbers etc, and
then during the process may manually insert were they think a part number exists, and
they often get it wrong. So it is extremely, extremely import when you use Excel, when
you use the Vlookup function and in particular when you do this approximate, to make sure
that the listing is sorted the way Excel wants it to be sorted, and not the way a human would
want to sort it.
My recommendation for this is that where at all possible avoid the approximate criteria,
unless you definitely know you want an approximate. Which means that you must not omit the formula
here, you must type false, and if the number does not exits it will then point out an error
to you and you are more likely to find it.