Harness the (Power) of Excel VLOOKUP


Excel VLOOKUP is quite possibly one of the most popular and yet misunderstood Excel functions. It is a powerful function that can be used to simplify and automate so many things in Excel.

As an Excel user advances in their skills, VLOOKUP typically becomes one of the next powerful tools used.

See how to use it, as any tool becomes more useful as the user’s knowledge and skill in manipulating it increases.

Start With the Basics

Basically, what VLOOKUP does is look in a column of a table of data for a value that you tell it to find. It then returns whatever value is in the same row of that table of data, but from a column that you tell it to look in.

The one restriction with VLOOKUP is that you can’t return a value that is to the left of the column that you are looking in. You can only return values in columns to the right.

Examine the Excel VLOOKUP Function

Here’s the function:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

  • lookup_value = the value you want to find in the table
  • table_array = the table of data that you want to look in
  • col_index_num = the column in the table of data that you want the value returned to you
  • range_lookup = logical value. If you want to find the closest match (TRUE) or an exact match (FALSE)

See How Excel VLOOKUP Is Used

The image below is a table that will be used for the VLOOKUP function. The store number of ‘1184’ will be looked up in the table to find that store’s phone number. It’s in cell A4 and highlighted in a red box.

vlookup-table-highlighted-value

Table of Data for VLOOUP

This is the formula that will be used to get the phone number for that store number using VLOOKUP:

=VLOOKUP(1184,A2:C5,2,FALSE)

The result of that formula will be (555) 555 6492, which is the value in cell B4.

Let’s break the formula down into plain English:

“Using the data in cells A2 through C5, find the exact match of the value ‘1184’ in left-most column. Once you find ‘1184’ in that column (which is always column 1), show the value in column 2 of that same row.”

The Real Power of VLOOKUP

In the example above, the value of “1184” was hard-entered into the formula. That was just done for the sample.

The real power of VLOOKUP is that you can pass a cell value, a formula result, or the result of a function to VLOOKUP.

Imagine that you have a table of data with those store numbers. It has hundreds, thousands, or even more rows. You want to know the address and phone number for each of those rows.

Just like this sample that has only a few rows:

data-needing-excel-vlookup

Sample Data Needing VLOOKUPs

All that you need to do is add two columns to the data – one for the address, and one for the phone number.

Now, add the VLOOKUP formula to those two columns, and instead of hard-entering the value that you want to look up, enter the cell address that has the store number.

This is what it will look like:

excel-vlookup-formula

The VLOOKUP Formulas

Copy that formula down in each column, using this technique, and you now have all of that information available.

Note, as seen in the formulas above, that you will need absolute references to the cells that are the data table if you want to use AutoFill to copy the formulas down. This ensures that each formula looks in the same, correct range of cells.

Here is the result:

excel-vlookup-result

Result of Adding VLOOKUPs

Conclusion

VLOOKUP is a great time saver that allows you to lookup values in a cell in a table of data and pull another value from the same row that it found the data in. The resulting data can also be used in a PivotTable. The possibilities are nearly endless.

With the PowerPivot feature being added to Excel you have an option to VLOOKUPs. See this post to read more about PowerPivot and how to use it instead of VLOOKUP.

Let me know if you have any tips or questions related to using the VLOOKUP function.

Leave a Reply