How to use VLOOKUP in Excel and Google Sheets

You give VLOOKUP a value to find, such as an email address. It searches the first column of a table for that value, goes across the same row, and returns whatever is in the column you asked for, such as the company name. The V stands for vertical: it searches down a column. Excel and Google Sheets both have it, and the formula is the same in both.

The most common job is joining two exports: a list of leads from one system and a list of accounts from another, matched on email so each lead gets its company, owner or status before the file goes into the CRM.

VLOOKUP takes four arguments, in this order:

The example: a sheet called Leads has email addresses in column A and an empty Company column B. A second sheet in the same workbook, Companies, has Email in column A and Company in column B. The formula fills in each lead's company from the Companies sheet.

Two things catch people out when the sheet with the VLOOKUP is the file they import.

First, the #N/A cells. Save the sheet as a CSV and each one is written as the text #N/A, which a CRM import reads as a real value. Every contact without a match ends up with #N/A as their company. Wrap the formula in IFNA to leave those cells blank instead: =IFNA(VLOOKUP(A2, Companies!$A$2:$B$500, 2, FALSE), ""). If you change only some cells this way, Excel marks them with a small green triangle because their formula differs from the cells around them. That is a note, not an error.

Second, the formulas themselves. They depend on the Companies sheet, so delete or move it and every cell turns to #REF!. Once the results look right, select the column, copy it, and paste it back with Home, Paste, Paste Values. The companies stay and the formulas go.

Between two sheets in the same workbook, the steps above are all there is: the sheet name and an exclamation mark go in front of the range, as in Companies!$A$2:$B$500. Excel adds the name for you when you click across to select. If the sheet name has a space in it, Excel wraps it in single quotes, as in 'Company list'!$A$2:$B$500.

Between two separate workbooks, open both first. Start the formula in the workbook that needs the values, then switch to the other one with View, Switch Windows, and select the range. Excel adds the file name in square brackets and makes the reference absolute on its own: =VLOOKUP(A2, [Companies.xlsx]Sheet1!$A$2:$B$500, 2, FALSE). If the file name has a hyphen or a space, Excel puts single quotes around the file and sheet, as in '[crm-contacts.xlsx]CRM'!$A$2:$B$301. If a CSV copy of the file is open too, Switch Windows lists both, so close the CSV first.

When you close the source workbook, Excel rewrites the reference to include the file's full path and keeps the last values it read. The next time you open the workbook with the formula, Excel warns that it links to external sources and asks you to choose Update or Don't Update. If you move or rename the source file, the link breaks, which is another reason to paste the results as values once they are right.

The formula is the same. Type =VLOOKUP(A2, Companies!$A$2:$B$500, 2, FALSE) into a Google Sheet with the same two tabs and it returns the same result. Google names the arguments differently (search_key, range, index and is_sorted), but they do the same jobs in the same order.

Google Sheets also assumes a sorted range unless you say otherwise, so it needs the FALSE at the end too. When you press Enter, Sheets usually offers Suggested auto-fill for the rest of the column: click the checkmark to accept it. Otherwise, drag the small square at the bottom right of the cell down the column.

The real difference is looking up from another file. VLOOKUP cannot point at a different spreadsheet directly, so you wrap the range in IMPORTRANGE with the other file's URL, as in the formula below. The first time, the cell shows #REF! until you connect the two files: click the cell and then Allow access. The range names the tab in the other file, so check its name: a spreadsheet made by importing a CSV names its tab after the file.

These are the usual causes, with the fix for each.

XLOOKUP is the newer function. It takes the column to search and the column to return as two separate ranges, so there is no column number to count and nothing breaks when a column is inserted. It finds an exact match unless you ask for something else, it can return a column to the left of the one it searches, and it has its own argument for what to show when nothing matches. The worked example becomes =XLOOKUP(A2, Companies!$A$2:$A$500, Companies!$B$2:$B$500, "").

XLOOKUP is in Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. It is not in Excel 2016 or Excel 2019, and a file that uses it shows errors when opened there. If anyone who opens the file is on an older version, stay with VLOOKUP.

INDEX MATCH is the older way to get the same flexibility, and it works in every version: =INDEX(Companies!$B$2:$B$500, MATCH(A2, Companies!$A$2:$A$500, 0)). MATCH finds the row number, INDEX returns the value from that row of the column you name, and the 0 asks for an exact match.

A VLOOKUP works one cell at a time, and the file has to be clean for it to work. Operelio's Match & pull columns does the same job on two whole files. Upload the file you want filled in and the file the extra columns come from, pick the shared column, and pick which columns to bring across. Every row is matched in one run and you download one combined file.

It also covers what a VLOOKUP cannot. It matches on more than one column at once, such as first name and last name together. Similarity matching catches typos and small differences, like Westmarch Trading in one file and WESTMARCH TRADING LTD in the other. You choose whether to keep every row, only the rows that matched, or every row from both files. The two files can be CSV or Excel, in any mix, and the originals are never changed.

All of it is on every plan, including Free, which reads up to 2,000 rows in each file.