Transcripts for the above video clip:

LOOKUP I PROJECT

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.