How to Write a VLOOKUP Formula in Excel

The VLOOKUP is part of a series of lookup and reference functions that allow you to pull information based on data in a particular row. VLOOKUP is my most used function in Excel as I am constantly working with separate data sets that I need to join together to pull over relevant information.

Setup

You have a spreadsheet with an Employee ID, Name, and Hours Billed per month. You have a second spreadsheet with Employee ID, Name, and Address. You need to bring this address over to the first spreadsheet in order to send these employee’s a paycheck based on their billed hours.

If the list of employees is small, you could filter by Employee and copy over each address individually; however, this is time-consuming and prone to user error. Let’s write a Vlookup formula instead to bring address over.

For Easier viewing, I have combined both sets of data into one sheet. In my experience however, you will get sent this information via two separate Excel workbooks. You could also combine these data sets via a query, but this type of access is typically restricted to IT and those in an Analyst type role.

Below is our starting point. We will be putting the Vlookup formula into column F to bring over address.

If you would like to follow along, please download the file attached below.
Excel_Vlookup

Vlookup 1

Writing a VLOOKUP

Steps:

  1. Label Column F as “Address”
  2. Hit the Fx function button
  3. Search for Vlookup and hit go
  4. Select Vlookup and hit OK

Your screen should look like the below
Vlookup2

Lookup_Value:
This is going to be your Employee ID, Column A.
Since we are placing this formula in cell F2, you need to reference the ID in A2.
Type A2 or left mouse click on cell A2

Table_Array:
This is going to be the set of Data or “Range” that you want to find your address in; in this case, J2 through M5.
Using the lessons learned in Navigate and Select Data, you can either:

  • Type in J2:M5
  • Select J2 with your mouse and drag the selection over to M5
  • Select J2 and press ctrl+shift+end
  • Select J2 and press ctrl+shift+down arrow and then ctrl+shift+right arrow
  • Select columns J through M or J:M

For the purposes of this exercise, I will do the last option and explain why further on.

Col_index_num:
This refers to the column in your Table Array that you want to see. In this example, Column M.
M is the 13th column on the spreadsheet, but it is the 4th column in your Table_Array so we will type in 4 to reference the 4th column.

Range_Lookup:
This refers to whether you want Excel to find an exact match “0” or an approximate match “1” I have never found a use for the latter so I always specify 0 in the box.

Steps cont’d

  1. Hit OK to complete your formula and you can see that it has brought over an address for Doe,John.
  2. Double click or drag down the black box in the bottom right corner of cell F2. Your cursor will change to a black + sign to let you know that you are on it

Your screen should look like the below.

Vlookup3

In the next post, we will go over ways to handle the #N/A formula errors as well as best practices for Vlookups.



Categories: Excel, Vlookups

Tags: , , , , , , , ,

1 reply

Trackbacks

  1. Vlookup Cont. Handling Errors

Leave a Reply

Show Buttons
Hide Buttons