How to VLOOKUP from Another Sheet in Google Sheets

By Gerard Fernandez · Updated · 4 min read

Most real spreadsheets keep lookup tables on their own tab, like a Products list or a price list. VLOOKUP can search a different tab by putting the tab name in front of the range.

The syntax

=VLOOKUP(search_key, SheetName!range, column, FALSE)

The only difference from a normal VLOOKUP is SheetName! before the range: the tab name followed by an exclamation mark.

Example

An Orders tab lists product IDs in column B. The prices live on a tab called Products, with IDs in column A and prices in column C. In C2 of the Orders tab:

=VLOOKUP(B2, Products!A2:C5, 3, FALSE)
Orders tab in Google Sheets: =VLOOKUP(B2, Products!$A$2:$C$5, 3, FALSE) in C2 returns 24 for P-103, then 4.5 and 9.25, and #N/A for the unknown ID P-109
Real test: an Orders tab looking up prices on a Products tab. The range is locked with $ so it could be filled down; P-109 isn't on the Products tab, so it shows #N/A.

You don't have to type the tab name: after =VLOOKUP(B2, , click the Products tab, select the range with the mouse, and Sheets fills in Products!A2:C5 for you. Then finish the formula and press Enter.

Tab names with spaces

If the tab name contains spaces or symbols, wrap it in single quotes:

=VLOOKUP(B2, 'Price list'!A2:C50, 3, FALSE)

In our test we renamed the Products tab to Price list. Sheets rewrote every formula on its own, quotes included, and the prices kept working:

After renaming the tab, the formula bar shows =VLOOKUP(B2, 'Price list'!$A$2:$C$5, 3, FALSE) and the prices are unchanged
Real test: after renaming the tab, the formula reads 'Price list'!$A$2:$C$5.

Copying the formula down

Lock the range with $ so it doesn't slide as you fill down, or use whole columns:

=VLOOKUP(B2, Products!$A$2:$C$50, 3, FALSE)
=VLOOKUP(B2, Products!A:C, 3, FALSE)

See absolute references.

From a different file

To look up data in another spreadsheet file, combine VLOOKUP with IMPORTRANGE:

=VLOOKUP(B2, IMPORTRANGE("spreadsheet_url", "Products!A2:C50"), 3, FALSE)

The first time, Sheets asks you to allow access between the two files (see "You need to connect these sheets"). More in how to use IMPORTRANGE.

Give the lookup table a name

If many formulas use the same table, name it once and use the name instead of Products!$A$2:$C$50:

  1. Go to the Products tab and select A2:C50.
  2. Choose Data → Named ranges, type a name such as Prices and click Done.
  3. Use it in the formula: =VLOOKUP(B2, Prices, 3, FALSE).

A named range never slides when you copy the formula, and it's much easier to read. If the table grows, edit the range once in the Named ranges panel.

Fill a whole column with one formula

Instead of dragging the formula down, one ARRAYFORMULA in C2 handles every order, including ones added later:

=ARRAYFORMULA(IF(B2:B="", "", VLOOKUP(B2:B, Products!A:C, 3, FALSE)))

The IF leaves rows without an ID empty instead of showing errors. In our test it filled the prices for all four orders from one cell (with #N/A for the unknown ID) and left the empty rows below blank. Keep the cells below C2 empty, or you'll get "Array result was not expanded".

Return several columns at once

To bring back both the product name (column 2) and the price (column 3), give VLOOKUP a list of column numbers in curly brackets:

=ARRAYFORMULA(VLOOKUP(B2, Products!A:C, {2, 3}, FALSE))

The result fills two cells side by side (in our test, "Desk lamp" and 24), so leave the cell to the right empty. If your spreadsheet uses semicolons between arguments, the list is written {2 \ 3}; see Formula parse error for why.

Choose the tab from a cell

If you have one tab per month (Jan, Feb, Mar…) and want to pick which one to search, type the tab name in a cell, say E1, and build the reference with INDIRECT:

=VLOOKUP(B2, INDIRECT("'" & E1 & "'!A:C"), 3, FALSE)

Change E1 from Jan to Feb and every formula switches tab. The single quotes inside the text make it work with tab names that contain spaces.

The XLOOKUP alternative

XLOOKUP does the same job with separate search and result columns, so the result can be on either side of the IDs, and it has a built-in "not found" value:

=XLOOKUP(B2, Products!A:A, Products!C:C, "Not found")

For the classic two-function version, see INDEX and MATCH.

Frequently asked questions

Why do I get #REF!?

Usually the tab name is misspelled, has spaces but no quotes, or the tab was deleted. In our test, typing Prodcts! gave #REF! with "Unresolved sheet name 'Prodcts'." (Renaming a tab is fine: Sheets updates the formulas for you, except names typed inside text for INDIRECT. We checked this too: after the rename, only the INDIRECT formula broke.) Also check that the column number isn't larger than the range. See how to fix #REF!.

Why do I get #N/A?

The ID isn't found in the first column of the range, often because of extra spaces or a range that moved when you copied the formula. See Fix "Did not find value in VLOOKUP evaluation".

Does the lookup update when the other tab changes?

Yes. As soon as a price changes on the Products tab, every VLOOKUP that uses it shows the new value.