The 10 Excel Formulas That Will Make You Look Like a Genius – Number 1 VLOOKUP

Excel is a wonderful and powerful program. Nearly every business uses it in some capacity because of its flexibility and utility. Did you know that there are several easy to learn formulas that can 10x your productivity in Excel? Over a series of articles, I am going to walk you through all ten. Today, let’s start with the first.

Number One – VLOOKUP

How many times have we seen VLOOKUP as the one formula that divides the wheat from the chaff when it comes to Excel knowledge? I can’t tell you how often I have seen a job description that said something like, “Advanced Excel knowledge/VLOOKUPs required.” If VLOOKUPs are the benchmark for dividing the advanced Excel users from the rest, let’s start there.

Before we get into the formula, let’s talk about what a VLOOKUP is supposed to do. Excel is a grid. It has multiple rows and multiple columns. Each row and column correspond to a number. The row numbers are easy to see because they’re clearly labelled on the left side of the Excel sheet. The columns are represented by letters, but in a lookup formula each of those letters also corresponds to a number. For example, Column A is 1, Column B is 2, etc.

In the example below, the data range (or table_array) would go from A1 to D5 (A1:D5). In this case, the rows are easy to see as 1, 2, 3, 4 and 5, and the columns are 1, 2, 3, and 4.

In the next example, the data (table_array) has been moved over. Even though our range now goes from B3 to E7 (B3:E7), the rows of the lookup formula are still 1, 2, 3, 4, and 5, and the column numbers are still 1, 2, 3, and 4.

So, we can see that the table is 5 rows by 4 columns no matter where we put it in the sheet.

A VLOOKUP formula will always start the search by first looking for a value in the first column. If it finds that value, it will then count over the number of columns you tell it and return whatever is in that cell.

If we wanted to know how old Jamal is in the table below, we would VLOOKUP “Jamal”, and then tell the formula to return the age, which is in column 3 of the table_array.

Now that we have that established, let’s take a look at the formula.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

To continue the example above, if we wanted to use the VLOOKUP formula to tell us how old Jamal is, we would click in a cell and type =VLOOKUP(“Jamal”,B3:E7,3,false).

WAIT! Why is it “False?” 

This one seems completely backwards to me, but let me try to explain. Excel gives us two options here: true and false. If we select “true”, Excel will attempt to return an approximate match. In all my years of using Excel I can only remember one time when I wanted to return an approximate match.  It was when I was referencing a list of incomplete data and getting some correct matches was better than getting no correct matches.

I use VLOOKUPs because I want to do one of two things. I either want to check to see if a value exists in a list (in this case I would check to see if the list even contains the name “Jamal”), or I know that a value exists in the list and I want to know something more about it.

For an exact match to the lookup, end the VLOOKUP formula with the word “false.”

And there you have it. You now know the basics of how to write a VLOOKUP formula.

Be sure to look for the next installment, when we will talk about the MATCH function.

If you would like to download a copy of the Excel file I used for this example, here it is:

–Chris Edney