How to VLOOKUP From Another Sheet in Excel: Formula, Examples and Fixes

shape
shape
shape
shape
shape
shape
shape
shape
How to VLOOKUP From Another Sheet in Excel: Formula, Examples and Fixes - featured image

VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row. In real work, the lookup table is often on a different sheet, such as a price list on one sheet and orders on another. The formula is almost the same as for a table on the same sheet; you only add the sheet name before the range. This guide shows the exact steps with a small order and price list example that you can check by hand, how to write the sheet name when it contains spaces, how to lock the range, how to hide errors, how to look up in a different workbook, what to do with several sheets, and why XLOOKUP is often a better choice. It also lists the common reasons why the formula fails.

Updated November 2026

In this guide

  1. The formula in one line
  2. Example data
  3. Step 1: start the formula
  4. Step 2: lock the range with dollar signs
  5. Step 3: calculate the total
  6. Sheet names with spaces
  7. Use a table or a named range
  8. Hide errors when the code is missing
  9. VLOOKUP from another workbook
  10. Lookup across several sheets
  11. Use XLOOKUP instead
  12. Check that the lists match
  13. Approximate match for price bands
  14. Why the formula fails
  15. Practice problems
  16. Quick recap

The formula in one line

The syntax is VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). For a table on another sheet, write the sheet name, an exclamation mark and the range inside the table_array argument. For example =VLOOKUP(A2,Prices!$A$2:$B$5,2,FALSE). This looks up the value of A2 in the first column of the range A2:B5 on the sheet named Prices, and returns the value from the second column. The last argument FALSE asks for an exact match, which is what you want in most cases.

Example data

Create two sheets. On the Prices sheet, put the product codes in column A and the prices in column B.

CodePrice
P1012.50
P10212.00
P10330.00
P10410.00

On the Orders sheet, put the order lines with the headers Code, Quantity, Price and Total in A1:D1. The codes in A2:A4 are P102, P104 and P101, with quantities of 3, 5 and 10. The prices will come from the Prices sheet.

Step 1: start the formula

On the Orders sheet, click C2, where the price should appear. Type =VLOOKUP( and click the cell A2, which holds the code. Type a comma. Now click the Prices sheet tab and select the range A2:B5. Excel writes the sheet name for you, and you will see Prices!A2:B5 in the formula. Type a comma, then 2 for the second column, a comma and FALSE, and close the bracket. Press Enter. For P102 the result is 12, which matches the price list.

Step 2: lock the range with dollar signs

When you copy the formula down, the range would move by one row each time, and the lookups would fail. To prevent this, make the range absolute with dollar signs: =VLOOKUP(A2,Prices!$A$2:$B$5,2,FALSE). Press F4 while the range is selected to add the dollar signs. Now copy the formula down to C3 and C4. The results are 10 for P104 and 2.5 for P101. The lookup value A2 stays relative, so it changes to A3 and A4 as the formula moves down.

Step 3: calculate the total

In D2 multiply the quantity by the price: =B2*C2. For the three lines, the totals are 3 times 12, which is 36, 5 times 10, which is 50, and 10 times 2.5, which is 25. The order total is 36 plus 50 plus 25, which is 111. You can also combine both steps in one formula, =B2*VLOOKUP(A2,Prices!$A$2:$B$5,2,FALSE), but a separate price column is easier to check and audit, so many people keep it.

Sheet names with spaces

If the sheet name contains a space or a special character, put it in single quotes. For a sheet called Price List, the formula is =VLOOKUP(A2,'Price List'!$A$2:$B$5,2,FALSE). If you click the range with the mouse, Excel adds the quotes automatically. If you type the formula by hand and forget them, Excel will show an error message. To avoid this, give your sheets short names without spaces, such as Prices or PriceList.

Use a table or a named range

A fixed range such as $A$2:$B$5 does not grow when you add a new product. Convert the price list to a table with Ctrl+T, name it PriceTable, and use =VLOOKUP(A2,PriceTable,2,FALSE). The table name works from any sheet, so you do not need the sheet name, and the range grows automatically. You can also create a named range, as explained in our named range guide. These two methods make the formula shorter and easier to read.

Hide errors when the code is missing

If the code is not in the price list, VLOOKUP returns #N/A. Wrap the formula with IFERROR to show a message instead: =IFERROR(VLOOKUP(A2,Prices!$A$2:$B$5,2,FALSE),"Not found"). A code such as P999 then shows Not found, while the valid codes still show their prices. Be careful with this, since IFERROR also hides other errors, such as a deleted sheet, so check the formula first. Our IFERROR guide explains when to use it and when to prefer IFNA.

VLOOKUP from another workbook

You can look up in a different file. Open both workbooks, start the formula and select the range in the other workbook. Excel writes the file name in square brackets, the sheet name and the range, such as [Prices.xlsx]Sheet1!$A$2:$B$5. If you close the source file, Excel adds the full path, so the formula keeps working, but it may ask to update links when you open the file. Links to other files break if you move or rename the files, so keep them in the same folder, or copy the price list into your workbook.

Lookup across several sheets

If your prices are split over two sheets, such as Domestic and Export, use IFERROR to try the second sheet when the first fails: =IFERROR(VLOOKUP(A2,Domestic!$A$2:$B$50,2,FALSE),VLOOKUP(A2,Export!$A$2:$B$50,2,FALSE)). For many sheets with the same layout, you can combine the lists into one table with Power Query, which is more reliable than a long chain of formulas. A single master list is easier to maintain, and it avoids the risk that the same code has different prices on different sheets.

Use XLOOKUP instead

In Excel 365 and Excel 2021, XLOOKUP is easier. =XLOOKUP(A2,Prices!$A$2:$A$5,Prices!$B$2:$B$5,"Not found") does the same job, with a separate lookup range and return range, so you do not need a column number. It also looks to the left, defaults to an exact match and has a built-in not found message. If you insert a column in the price list, the VLOOKUP column number may become wrong, while XLOOKUP keeps working. See our XLOOKUP guide for the full explanation.

Check that the lists match

Before you blame the formula, compare the two lists. Select a code in the Orders sheet and test it with =COUNTIF(Prices!$A$2:$A$5,A2). A result of 1 means that the code exists once in the price list, 0 means that it is missing or written differently, and 2 means a duplicate, which can lead to the wrong price. You can also use =ISNUMBER(MATCH(A2,Prices!$A$2:$A$5,0)) to get TRUE or FALSE. Fix the data once, and every formula that depends on it will work. A short check column saves a lot of time when a list has hundreds of rows.

Approximate match for price bands

The last argument TRUE, or leaving it out, asks for an approximate match. It is useful for bands, such as a discount of 5 percent from 100 units and 10 percent from 500 units. The first column must be sorted in ascending order, with 0, 100 and 500, and VLOOKUP returns the row of the largest value that is not above the lookup value. For 250 units the result is the 100 band, which gives 5 percent. For prices and codes always use FALSE, because an approximate match can return a wrong row without any warning.

Why the formula fails

The most common reason is that the lookup value does not match exactly, for example a code with a trailing space, or a number stored as text in one list and as a number in the other. Use TRIM to clean the text and check the data types. Another reason is that the lookup value is not in the first column of the range, because VLOOKUP only searches the first column. A third reason is a wrong column number. A fourth is the missing FALSE, so the result is approximate. Our VLOOKUP not working guide covers these in detail.

Practice problems

Try these with the example. Problem 1: what is the price of P103? 30. Problem 2: what is the total for 4 units of P103? 120. Problem 3: write a formula that returns Not found for P999. Problem 4: if the price list is a table called PriceTable, write the formula for the price of the code in A2. Problem 5: change the price of P101 to 3 and check that the total of the order changes from 111 to 116, since 10 times 0.5 is 5.

Quick recap

Write VLOOKUP as usual, but select the lookup range on the other sheet, so Excel adds the sheet name and an exclamation mark. Lock the range with dollar signs, use quotes around sheet names with spaces, and ask for an exact match with FALSE. Use a table or a named range so the lookup grows with the data, wrap the formula in IFERROR for missing codes, and consider XLOOKUP in newer versions. Keep the lookup values clean and the formula works every time.

Frequently asked questions

How do I VLOOKUP from another sheet?

Select the lookup range on the other sheet, so the formula includes the sheet name and an exclamation mark.

Why do I need dollar signs?

They keep the lookup range fixed when you copy the formula down.

How do I write a sheet name with spaces?

Put it in single quotes, such as ‘Price List’!A2:B5.

Can VLOOKUP use another workbook?

Yes, the formula includes the file name in square brackets.

Is XLOOKUP better than VLOOKUP?

In many cases yes, because it does not need a column number and can look to the left.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *