Excel VLOOKUP

What is a lookup ?
How VLOOKUP works
VLOOKUP across tabs
Approximate vs Exact Match
HLOOKUP
SQL Equivalent of VLOOKUP
#N/A error
Number as Text
VLOOKUP works Left to Right only
Wildcard or Partial Matches
LOOKUP is case insensitive
Merge data using VLOOKUP
Named Ranges
VLOOKUP in Google Sheets
Sample file

What is a lookup ?

Lookup is one of the most commonly used functions in excel. Let’s understand what it is with an example.

Say, we have orders data from an e-commerce website like so.

What we have are the list of products and its prices. Say, somebody gives you a subset of these products and tells you to pick up the corresponding prices. How would you do it ? Of course you can do it manually if you are just talking about a couple of items. However, once you have a handful of items, you need a method for it. Let’s see how we can do it using VLOOKUP formula in excel.

For example, we want to find out the price of 3 items that you see on the right. If we want to get them using VLOOKUP, this is how we do it.

The VLOOKUP formula looks like a big deal, but it is actually not. We will look at the actual parameters in the next chapter, but first I want you to understand what VLOOKUP can do. In the example above, we were able to pull the prices of 3 items. What if there were a million items in the lookup data table and you are trying to find out the prices of 2000 items from it ? As you very well know, this cannot be done manually – This is where VLOOKUP works its magic.

How VLOOKUP works

Now that we understand what VLOOKUP can do for us, let’s understand how VLOOKUP works at a high level. There are 4 parts to the VLOOKUP formula.

Lookup Data ( or lookup table )
Item or key to be looked up
Column number in the lookup data that needs to be extracted
Exact vs approximate match

The following slideshow highlights each of these with the corresponding visuals. What we are trying to do here is to look up the price of item code 22697.

What we have actually done is to just get the prices of 3 item codes. It might look trivial, but imagine the following data – A half a million rows of online retail data from a real store.

Now, say you are given 1000 item codes and asked to look up the price. Imagine what a pain that would be to do manually. VLOOKUP can do this in a split second.

VLOOKUP across tabs

VLOOKUP not just works within a tab. It can work across tabs. Let’s take the same Online Retail product price list with half a million records. Let’s take a subset of these items, say 500 of them and now, we want to look up the prices of these items.

We could do the look up in the same tab, but that makes it clumsy. Instead, let’s do it in a separate tab. To reference the lookup data in a separate tab, all you have to do is to prefix the tab name – like so.

Approximate vs Exact Match

So far, we have been doing Exact matches off the key with the lookup data. Sometimes, we might have to do an approximate match. Approximate match is NOT a wildcard match as you might expect. We will deal with wildcard matches in a seperate section down below. Let’s understand what approximate match does with an example.

Imagine a sales team with different commision percentages.

Now, say we have 4 sales persons with different sales volumes and we want to find out the percentage commision each of them should get.

You see, in this case, the sales volume does not EXACTly match the slabs. Instead, it is a bracket. In cases like this, you can use the approximate match feature of VLOOKUP.

The moment you set the match option to TRUE ( which also happens to be the default option ), an approximate match happens (as opposed to an exact match). You can see the results for yourself. Although the sales numbers on the left don’t exactly match with the slabs on the right, VLOOKUP is smart enough to identify which slab each of the sales volumes fall under and matches the percentage accordingly.

HLOOKUP

While VLOOKUP is the most used function (among VLOOKUP & HLOOKUP), Excel gives you the option to do HLOOKUP or Horizontal lookup as well. Look at the same Retail example flipped horizontally below.

The data is exactly the same, except it is set up horizontally – hence HLOOKUP.

SQL Equivalent of VLOOKUP

If you have some exposure to SQL, VLOOKUP works like a SELECT statement with a LIKE clause. The equivalent of the above VLOOKUP in SQL would be something like this

SELECT item_desc, price FROM <lookup table>

WHERE item_number IN (<list of item numbers>)

#N/A error

What if the item that is looked up doesn’t exist ? Excel throws a #N/A error.

Number as Text

Sometimes, when the key in the lookup data is formatted as text, the lookup does not work and would result in an error.

Select the data, right click on it and click on “Convert to Number”. That should fix the problem.

VLOOKUP works Left to Right only

VLOOKUP always works Left to Right – meaning, the key should always be the first element in the lookup data.

Wildcard or Partial Matches

Sometimes, you might do text based matches. For example, in the example below, you might want to extract the prices using item description, which is a text field ( as opposed to item code, which is numeric). In cases like this, you might not get the text exactly right.

That’s when you can use wild card search. Just append the search key with a “*” (asterisk) and you should be good to go.

LOOKUP is case insensitive

Text based lookup is case insensitive. You can try it by changing cases in the example sheet above.

Merge data using VLOOKUP

Merging data from multiple sources ( typically separate tabs or separate sheets) is one of the use cases for VLOOKUP. The only constraint being that the data should be bound by a common key.

The simple example above shows data being merged from 2 look up tables. You can extend the same idea across multiple tabs or sheets. All you have to remember is that there should be a common key across the data being merged.

Named Ranges

The way number ranges are specified in excel when VLOOKUP is being used is a bit ugly looking. You can use named ranges when doing lookups. Instead of specifying lookup data using rows and columns, you can very well used named ranges. For example, say we specify a name for the lookup data like this..