VLOOKUP from Another Sheet: How To

To use VLOOKUP from another sheet in Excel or Google Sheets, you simply add the sheet name and an exclamation mark before the range, like =VLOOKUP(A2, Sheet2!A:B, 2, FALSE). This tells the formula to search for the lookup value in the range you specify on that other sheet and return a matching value from the column you choose.

Formula

The syntax for VLOOKUP is the same whether you reference the current sheet or another one:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value – the value you want to find (e.g., a product ID or name).
• table_array – the range that contains the lookup value and the result. For another sheet, this is written as SheetName!Range (e.g., Sheet2!A:B). If the sheet name contains spaces, put it in single quotes: 'My Sheet'!A:B.
• col_index_num – the column number in the range from which to return the value. The first column of the range is 1.
• range_lookup – FALSE for an exact match, TRUE (or omitted) for an approximate match. For most lookups, use FALSE.

Worked Example

Suppose you have a list of orders on a sheet named “Orders� and you want to pull the customer name from a sheet named “Customers�. The Customers sheet has customer IDs in column A and names in column B.

On the Orders sheet, cell A2 contains the customer ID, and you want the name to appear in cell B2. The formula would be:

=VLOOKUP(A2, Customers!A:B, 2, FALSE)

1. Lookup value: A2 (the customer ID on the Orders sheet).
2. Table array: Customers!A:B – this refers to the entire columns A and B on the Customers sheet.
3. Column index: 2 – because the name is in the second column of the range.
4. Exact match: FALSE – you want the exact customer ID.

If the customer ID in A2 is 1001 and the Customers sheet has 1001 in A5 with “Acme Corp� in B5, the formula returns “Acme Corp�.

How to Reference Another Sheet

There are two ways to enter a cross-sheet reference:

• Type it manually – write the sheet name, an exclamation mark, and the range. For example: Sheet2!A:B.
• Click the sheet tab – while entering the formula, click the tab of the other sheet, then select the range. Excel or Sheets will insert the correct reference automatically, including quotes if the name has spaces.

If the sheet name has spaces or special characters (like “Sales Dataâ€�), you must enclose it in single quotes: 'Sales Data'!A:B. If you forget the quotes, the formula returns a #REF! error.

Common Mistakes and How to Avoid Them

Several errors are common when using VLOOKUP across sheets:

• #N/A error – the lookup value is not found in the first column of the range. Check that the value exists and that there are no extra spaces or formatting differences (e.g., text vs. number).
• #REF! error – the column index is larger than the number of columns in the range. If your range is A:B and you ask for column 3, you get #REF!.
• #VALUE! error – the lookup value is not the same data type as the first column, or the range is invalid.
• Wrong result (not an error) – if you use TRUE (approximate match) and the first column is not sorted, you may get a wrong match. Always use FALSE for exact matches.
• Sheet name typos – a misspelled sheet name causes a #REF! error. Double-check the name and the quotes.

VLOOKUP vs. Other Lookup Methods

VLOOKUP is not the only way to get data from another sheet. Depending on your situation, you might consider:

• INDEX/MATCH – more flexible than VLOOKUP because it can look to the left of the lookup column and does not break if you insert a column in the middle of the range. The syntax is =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).
• XLOOKUP – available in newer Excel versions and Google Sheets. It is simpler and handles arrays, left lookups, and default values. Syntax: =XLOOKUP(lookup_value, lookup_range, return_range).
• QUERY (Google Sheets) – for more complex joins, you can use =QUERY(Sheet2!A:B, "select B where A = "&A2, 0).

For most simple lookups, VLOOKUP is fine. If you need to return multiple columns or look to the left, consider INDEX/MATCH or XLOOKUP.

Performance and File Size

When you use VLOOKUP across sheets, Excel or Google Sheets must read the entire range you specify. If you reference whole columns (e.g., A:B), it scans all rows, which can slow down large files. To improve performance:

• Use a limited range (e.g., A1:B1000) instead of full columns.
• Sort the first column if you use approximate match (but you usually want exact match).
• In Google Sheets, consider using ARRAYFORMULA with VLOOKUP for multiple rows, but be aware of calculation limits.
• Keep the lookup column as the first column of the range – VLOOKUP cannot look to the left.

Frequently Asked Questions

How do I reference a sheet with a space in its name?

Put the sheet name in single quotes: ='My Sheet'!A:B. For example: =VLOOKUP(A2, 'My Sheet'!A:B, 2, FALSE).

Why does VLOOKUP return #N/A even though the value exists?

The most common cause is a mismatch in data types or trailing spaces. For instance, if the lookup value is a number but the first column contains text (or vice versa), the exact match fails. Also, check for extra spaces using TRIM() or use VLOOKUP(A2, Sheet2!A:B, 2, FALSE) with the lookup value wrapped in TRIM().

Can VLOOKUP pull data from a different workbook?

Yes, you can reference an external workbook by including the file path in brackets, like ='[Budget.xlsx]Sheet1'!A:B. However, the other workbook must be open in Excel for the formula to update; otherwise, it returns the last calculated value.

What is the difference between FALSE and TRUE in VLOOKUP?

FALSE requires an exact match – the lookup value must appear exactly in the first column. TRUE allows an approximate match, which finds the largest value that is less than or equal to the lookup value, but the first column must be sorted in ascending order. For most lookups, use FALSE.

How do I use VLOOKUP with a dynamic sheet name?

You cannot directly use a cell reference for the sheet name in VLOOKUP. However, you can use the INDIRECT function: =VLOOKUP(A2, INDIRECT(B1&"!A:B"), 2, FALSE), where B1 contains the sheet name. Be cautious: INDIRECT is volatile and can slow down large workbooks.

Does VLOOKUP work in Google Sheets the same way?

Yes, the syntax is identical. The only difference is that Google Sheets may handle some edge cases slightly differently (e.g., wildcard characters). The formula and sheet reference rules are the same.

Related

• Nova Lake vs AMD Ryzen Desktop CPUs: Which Fits Your Build?
• How to Customize Your iPhone Lock Screen Access
• Linux Kernel Regression: What to Verify Before Filing

in lookup | Lookup value | - | 1001 | 0
in sheet | Sheet name | - | Sheet2 | 0
in col | Return column | - | 2 | 1
out result | VLOOKUP result | - | 0 | 0