How to VLOOKUP from another sheet in Excel

The formula, step by step

You have orders on one sheet and a price list on a sheet called Price List, with SKUs in column F, product names in G and unit prices in H. On the orders sheet, the price for the SKU in B2 is =VLOOKUP(B2,'Price List'!$F$2:$H$7,3,FALSE). Everything is the same as a normal VLOOKUP except the table argument, which now starts with the sheet name.

The sample below keeps the price list next to the orders so you can see the result. The answer is identical when the range points at the other sheet.

1. Click the cell where the price should go and type =VLOOKUP(B2, (with the comma).
2. Click the Price List sheet tab at the bottom of the window. Excel writes 'Price List'! into the formula for you.
3. Select the table, F2:H7, then press F4 once to make it $F$2:$H$7.
4. Type ,3,FALSE) and press Enter. Excel jumps back to the orders sheet with the result.
5. Fill the formula down the column.

When the quotes are needed

A sheet name with no spaces needs no quotes: =VLOOKUP(B2,Prices!$F$2:$H$7,3,FALSE). As soon as the name has a space, a hyphen or other punctuation, or starts with a number, it must be wrapped in single quotes: 'Price List'!, 'Q4-2026'!, '2026'!. Quotes around a simple name are allowed too, so when in doubt, add them.

If the sheet name itself contains an apostrophe, type it twice inside the quotes: 'Bob''s Prices'!$F$2:$H$7. The easiest way to avoid every one of these problems is the click method above, because Excel writes the reference correctly for you.

Typing the name by hand and getting it slightly wrong (Price list vs Price Lists) does not give an error message straight away. Excel asks you to locate a file to update values from, because it thinks you mean another workbook. Press Cancel and fix the name.

Lock the range, or lower rows go missing

When you click a range on another sheet, Excel writes it as a relative reference (F2:H7). Fill the formula down and it slides: row 3 looks in F3:H8, row 7 looks in F7:H12. The rows at the top of the price list drop out of the search, and lookups for those SKUs start returning #N/A even though they are in the table.

Here BOT-75 is the first row of the price list, so row 7's copy of an unlocked formula cannot find it. Press F4 after selecting the table to add the $ signs, or point at whole columns: 'Price List'!$F:$H.

The XLOOKUP and INDEX/MATCH versions

XLOOKUP takes two separate ranges, and both need the sheet name: =XLOOKUP(B5,'Price List'!$F$2:$F$7,'Price List'!$H$2:$H$7,"Not in price list"). It needs no column number, so inserting a column in the price list does not shift the result, and the fourth argument gives a clear message for SKUs that are missing. XLOOKUP is in Excel 365, 2021 and 2024.

INDEX and MATCH work in every version: =INDEX('Price List'!$H$2:$H$7,MATCH(B7,'Price List'!$F$2:$F$7,0)) returns 11.5 for BOT-75. All three ignore upper and lower case, so the lowercase mug-12 in B6 still finds MUG-12.

Look up from another workbook

With both files open, start the formula, switch to the other workbook (View > Switch Windows, or Ctrl+Tab), click the sheet and select the range. Excel writes the file name in square brackets: =VLOOKUP(B2,'[Prices.xlsx]Price List'!$F$2:$H$7,3,FALSE). The quotes wrap the file and sheet name together.

Close the price file and Excel expands the reference to the full path, for example 'C:\Users\Sam\Documents\[Prices.xlsx]Price List'!$F$2:$H$7. VLOOKUP and INDEX/MATCH keep working with the source closed. SUMIF, SUMIFS, COUNTIF and COUNTIFS do not: they return #VALUE! until the source file is open.

When you open the orders file later, Excel shows a security warning about links to external sources. Enable content to refresh the prices. Data > Edit Links (Workbook Links in newer versions of Excel 365) lists every linked file and lets you update, change the source or break the link.

#REF! and broken links: renamed or deleted sheets

Renaming a sheet inside Excel is safe: every formula that points at it updates to the new name automatically. Deleting the sheet is not. Formulas that pointed at it change to #REF! and the original reference is gone, so Undo (Ctrl+Z) straight away or rebuild the formula.

With a separate workbook, renaming or moving the file outside Excel breaks the link, because the formula still stores the old path. When you open the file, Excel warns that some links cannot be updated. Fix it in Data > Edit Links > Change Source and pick the file at its new name or location.

If you build the sheet name from text with INDIRECT, such as =VLOOKUP(B2,INDIRECT("'"&J1&"'!F2:H7"),3,FALSE), Excel cannot update it when the sheet is renamed, and you get #REF!. INDIRECT also returns #REF! when it points at a workbook that is closed. Use it only when the sheet name genuinely has to change from a cell.

Which method to use

1. Excel 365, 2021 or 2024 and a table on another sheet: XLOOKUP with its if_not_found message.
2. File shared with people on Excel 2019 or older: VLOOKUP with FALSE, or INDEX/MATCH if the return column is left of the key.
3. Source in another workbook that is often closed: VLOOKUP or INDEX/MATCH, not SUMIF or COUNTIFS.
4. Price list that grows: turn it into an Excel table (Ctrl+T) and use the table name, so new rows are included without editing the formula.