How to Use VLOOKUP in Excel or Google Sheets

VLOOKUP is more than a spreadsheet function; it's how you become a workplace hero. When you have two spreadsheets with different, but related sets of data like a list of customer addresses and a separate list of customer purchases, you can combine the data into one sheet. It's not hard to learn how to how to create a VLOOKUP formula, but most of your coworkers probably don't have this knowledge (and you don't have to tell them).

Over the years, I've used VLOOKUP to remain indispensible at all of my jobs in web publishing, because we often have a list of article URLs we want to track and then a giant CSV of all traffic data by page from Google Analytics. Here's how to use VLOOKUP in Microsoft Excel or Google Sheets. While the screen shots here are from Excel, the process works exactly the same way in Sheets.

1. Put both sheets into the same file, but in separate tabs. If you have the data you want to combine in separate files, create a new tab in one of them and copy over the data from the other.


For example, I started with a list of articles we wrote at Computex then downloaded a spreadsheet with traffic data from Google, which I moved over to a tab on the first sheet (numbers here are fake).

2. Navigate to the tab where you want to import the numbers and click into the first cell where you want them to appear. In my case, it was the tab with the list of Computex articles.


3. Type =VLOOKUP( then enter the cell number for the field you want to match followed by a comma. Since the article URL is the common field in my case, I'm selecting cell A2. 


4. Select all the cells that contain data you want to import on the other tab. I recommend selecting the entire columns. Excel or Sheets will fill in the tab name and cell range (ex: 'All Traffic'!A:E). 


5. Highlight the cell range in the formula and hit F4. The application will put dollar signs on the cell range to signify that these are absolute addresses, which means that you won't need to redo this part of the formula if you paste it into another cell.


6. After inserting a comma, enter the column number of the data you want to copy over to the primary sheet. In our case, the Page Views column we want to copy is second from the left (column B), so it is number 2. Note that the column number you need is the one from the data sheet, not the primary sheet.  


7. Enter comma followed by the word FALSE and then a close parenthesis. Entering FALSE ensures that VLOOKUP accepts only an exact match. That's your complete formula.


8. Copy and paste the formula down the entire column.


If you want to import other columns, copy the formula over to a cell in another column, but be sure to change the destination cell (in our case, from 2 to 3 or 4) and keep the matching cell at what it was (A2 in our case).

Create a new thread in the UK Article comments forum about this subject
This thread is closed for comments
No comments yet
Comment from the forums
    Your comment