Home » Excel » How to Write a VLOOKUP Formula in Excel

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.


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.

Vlookup 1

Writing a VLOOKUP


  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

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

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.

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.

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.


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

1 Comment

  1. […] the prior Vlookup post we reviewed how to create a basic Vlookup formula; combining employee hours with employee […]

Leave a Reply

%d bloggers like this: