VLOOKUPS – What is the big deal anyway?

Why is VLOOKUP so awesome? The purpose for VLOOKUP is to help you find a single piece of information in a table based on a single criterion. 

Watch my video tutorial and learn all about it:

Suppose you’ve downloaded a client list from your CRM and it contains things like Internal Client IDs, addresses, phone numbers, and open balance amount (among tons of other pieces of information). Your manager tasked you with putting together an account statement in Excel for ten clients, and the account statement needs to show the Client’s information on the header so that it looks professional. The statement must also include a list of open invoices. VLOOKUP, among other things, can help you build a dynamic template for your account statement so that you can use it repeatedly for any number of clients by changing just a few cells.

The VLOOKUP helps you achieve this by searching for data (ie. address or open balance) within a table by using one data point (ie. Client ID) just as long as that data point is in the first column of your table.

Theory behind how it works

Before I dive into how to write the function, I want to discuss how it works in theory first. This way, when we talk about how to write the function and use it you’ll already have a foundation of understanding.

Here is the Client ID you want to search for:

Here is the table you are searching in:

In this example, the VLOOKUP function will search for Client ID 1002 within the data table and return the client’s phone number. But how will excel know to return the phone number? You’re going to tell it to by entering the column number where the phone numbers exists into the formula, that’s how!

By entering Column 4 into your VLOOKUP function, excel will return whatever exists in Column 4 (in this case, a phone number for client 1002). Now, lets bring it full circle:

 

=VLOOKUP(A2,A7:E11,4,FALSE)

Now that you understand the theory a little better, lets breakdown the VLOOKUP function!

 

The Syntax

Lookup Value – This is the criterion you intend to use in order to “V-LOOKUP” the other table. In our example, this was the Client ID. You can use whatever is best, but just keep these two things in mind:

 

1. Make sure that whatever you use as your criteria, it is located in the first column of your data table. If not, your VLOOKUP will return #N/A (I hate those).
2. Your criteria must be spelled the same way in your table. For example, if you use “John Doe” as your VLOOKUP criteria, it can’t show up as “John H. Doe” in your data table. If this is the case, your VLOOKUP will return #N/A.

 

Data Table – This is the table that contains all the data you need your VLOOKUP to return.

 

Column Number – The VLOOKUP’s bread and butter. This is where you tell your function where to look.

 

Pro Tip: I find it useful to number my columns in the first row, especially if I’m working with a large table. This way, I avoid wasting time counting columns all day.

 

True or False – Lots of people get hung up over this. Don’t worry about it! I’ll save you some headaches and say that 99% of the time I enter FALSE. This tells excel whether your function is going to be an exact match (tell me exactly what I need [FALSE]) or an approximate match (tell me something close to what I need [TRUE]).

 

HLOOKUP

If you understand the VLOOKUP, then the HLOOKUP will be a walk in the park. HLOOKUP is VLOOKUP’s twin brother; it does the same thing except instead of searching columns for the solution it will search rows. In my career I’ve seldom used the HLOOKUP since most data tables are structured in a way that makes VLOOKUP more useful.