Why VLOOKUP Isn’t as Precise as You Might Think (And How To Fix It!)
By 
Transcription
So today, I'd like to show you that the VLOOKUP function, by default, is really not as precise as you think it is, unless you understand how it works. The same applies to HLOOKUP. So, as you can see, the file in front of you right now is kind of like a phone book - on the right hand side, you can see I have names and their associated phone numbers. I can scroll down, you'll see I have a bunch of these. And on the left-hand side I have a few features - so there's a text box here that we'll be interacting with shortly. This is where I can type in a name, and if there's a match, it's going to appear right here where it says phone number. You can also see that there's 201 phone book entries right here, and I also added a little feature here that shows how many entries start with each letter.
Okay! So let's start. The VLOOKUP function is amazing, here it is - I use it all the time, it is super useful in Excel. So let's start by looking up a name. I'll do the easiest, I'll select "Adams, Olivia", which is the one immediately to the right of this cell. So I'll say "Adams, Olivia" - as you can see, my sheet reacts. It found the match, Adams, Olivia, it highlights it for me, this is because of some conditional formatting I have here, and then down here, it selects the phone number. You can see 175-2344. Cool! Now, let's try another name, I'm going to select now, "Baker, Nicola", okay. So it found 575, which we can see here, 9527, but... wait a minute, that's not... look at the name next to it: Kelvin? Kelvin Baker? Nicola Baker? That's not the same thing. Nicola Baker's right here. Why is it assuming Kelvin? Hm. And that's my point. By default, VLOOKUP does not look for an exact match. That revelation has completely changed the way I use VLOOKUP moving forward. So I'll show you how to fix this.
As you can see right here, this is where the VLOOKUP is, as I showed earlier, you can click up here, right here, there's a Lookup Value, which is, you know, C2, right here. It also has a Table Array, which you can see here, I've selected it. And it has a Column Index Number which is the second column in this case. So we're looking up here, but we're finding the second column, okay? Which is how the number shows up here. But here's the interesting bit - Range Lookup, what's this? So let me go ahead and actually put a comma, and you'll see, oh, what? Approximate match: true. Hm. So by default, let's be clear - true is selected. So the fact that I didn't have it meant that approximate match was on. I don't know about you, but when I'm in Excel... I want it to be precise. It's Excel. You know?
So let's go ahead and click false - exact match. I double-click it - see how false appears here? Now I'm just gonna press this, enter, and look at that! Hm, Nicola Baker, 837 - yeah! That's the right number.
Now, let's take a step back here. Let's actually go back to the previous mode, okay? And look at this. Look at the way that they're ordered here. So it's clearly alphabetical, right? So Bailey Edwin, Bailey Kelsey, Bailey Naomi, Baker Carlos, Kelvin - okay, and then we have Nicole, hmmm. If it's an approximate match, how is Nicola not even reached, and it concludes Kelvin. I don't know what Excel is doing internally, but it's getting it wrong. And we can find out more about what Excel is thinking by actually clicking on one of the links when we're working on the formula. So for example, I'm going to click this, and then I'm going to click inside the parameters for the VLOOKUP, and then you'll see there's a little tooltip that appears. And in this tooltip, I'm actually going to click VLOOKUP - this will pop up the Help in Excel.
Now Microsoft's documentation, I gotta hand it to them, I think it's great. So on the right hand side, let's scroll down through the help - and then you see the Technical Details section here, I'm going to click it, I'm gonna keep scrolling, and what we're interested in is the fourth parameter. So let's go back here to the formula, I'll show you quickly. The fourth parameter, one, two, three, four, is range_lookup, so let's find it. And here we have range_lookup on the right, in the technical section. So, what is it? Well, it's "[a] logical value that specifies whether you want VLOOKUP to find an approximate or an exact match" - okay.
Now let's found how the approximate match works. "[It] assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value." Okay. And yes, it is the default method as I told you. And then exact it "searches for the exact value", which, you know, you would want, if you're building a phone book. But anyway, I'm going to close the help.
So we know it's sorting alphabetically, so let's figure out why we're not getting the result we expect. Well if we look carefully in this list here, this isn't actually sorted alphabetically, I mean it is - the only problem is that Nicola over here is out of order, she's supposed to be higher up. If we actually make this change, we might actually get the result that we expect. So for example, I'm going to change Nicola here, I'm going to put an A on Nicole, put an E on Nicola, and as you can see it now pulls up the right phone number. So again I don't understand why Kelvin was approximately close to Nicola, but fixing the alphabetical sequence actually corrects the problem.
Now, this means that that parameter in the VLOOKUP actually can have its uses. So for example if you know your data is sorted alphabetically, putting a "true" here will probably speed up the results, especially if you have a huge dataset. But for me, the idea that Excel is not providing me with exact results is unacceptable, so I always set this to false, and if I ever spot a VLOOKUP that's not set to false, I set it to false immediately, because I want to precision.
And so that's it!