To use VLOOKUP in Excel between 2 sheets, type a formula that points at the second sheet by name: =VLOOKUP(B2,Prices!$A$2:$C$6,3,FALSE). It looks for the value in cell B2, searches the first column of the range A2:C6 on the sheet called Prices, and returns the matching value from the third column. The FALSE at the end asks for an exact match. This guide works through one example — an orders sheet pulling prices from a second sheet.
What VLOOKUP Does In Excel
VLOOKUP stands for vertical lookup. It finds a value in the first column of a table and returns a value from the same row — you ask Excel, “Here is a product code, what is its price?” and it searches the table for the answer.
This matters because real data is almost never in one place. Prices live on one sheet, orders on another; employee names sit in one table, salaries in another. VLOOKUP is the bridge: keep your source data in one sheet, write a short formula in the other, and Excel pulls the matching values across. Microsoft describes it as the function to use “when you need to find things in a table or a range by row” — see Microsoft’s official VLOOKUP function page for the full definition.
One limit shapes everything that follows: VLOOKUP only searches the first column of the range you give it, and only returns values from columns to its right — so the value you search for must sit in the leftmost column, and the answer to its right.
The VLOOKUP Formula, Explained Piece By Piece
The full syntax, as Microsoft documents it, is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). There are four arguments, and only the last one is optional.
Lookup_Value: The Value You Want To Find
The lookup_value is the value you are searching for: a number, a piece of text, or a reference to a cell that holds the value. In most real work you will point at a cell, because the point is to repeat the lookup for many rows. In our example, the lookup value is B2, the product code on the Orders sheet.
Table_Array: The Range Where Excel Searches
The table_array is the range holding both the value you are searching for and the value you want back. The lookup value must be in its first column, and the range must include the column holding the answer. On another sheet, the range includes the sheet name and an exclamation mark, like Prices!$A$2:$C$6, and the dollar signs lock it in place for copying down.
Col_Index_Num: Which Column Holds The Answer
The col_index_num is the number of the column inside your table_array that contains the value you want returned, counting from 1 for the leftmost column of the range — not the worksheet. In Prices!$A$2:$C$6, column A is 1, column B is 2, and column C is 3 — so a price in column C means the column index number is 3.
Range_Lookup: Exact Match Or Approximate Match
The range_lookup is the only optional argument. Type FALSE, or 0, for an exact match, or TRUE, or 1, for an approximate match. If you leave it out, Excel uses TRUE by default. For most day-to-day lookups — product codes, employee IDs, order numbers — you want FALSE. An approximate match assumes the first column is sorted and returns the closest value below the one you searched for — fine for tax bands, dangerous for exact prices.
How To Use VLOOKUP In Excel Between 2 Sheets: A Worked Example
Imagine a small online shop. The Orders sheet lists orders to price; the Prices sheet holds the master price list. The task: pull the correct price for each order without typing any price by hand.
The price list on the Prices sheet (A1:C6):
| Product Code | Product Name | Unit Price |
|---|---|---|
| P-101 | Wireless Mouse | 24.99 |
| P-102 | USB-C Hub | 39.50 |
| P-103 | Mechanical Keyboard | 59.00 |
| P-104 | Laptop Stand | 34.95 |
| P-105 | Webcam HD | 49.99 |
The Orders sheet, with Unit Price waiting to be filled in:
| Order ID | Product Code | Quantity | Unit Price |
|---|---|---|---|
| ORD-201 | P-103 | 2 | |
| ORD-202 | P-101 | 1 | |
| ORD-203 | P-105 | 3 | |
| ORD-204 | P-102 | 1 |
Step 1: Click The Cell Where The Answer Should Go
Click cell D2 on the Orders sheet, the first empty cell under Unit Price.
Step 2: Type The Formula And Point At The Lookup Value
Type =VLOOKUP( and then click cell B2, the cell holding the product code P-103. Excel enters B2 as the lookup value. Type a comma.
Step 3: Select The Table On The Second Sheet
Click the Prices tab, then select A2:C6 by dragging from A2 down to C6. The range starts at row 2, so the header row is left out, and Excel writes the reference as Prices!A2:C6. Type a comma.
Step 4: Lock The Range With Dollar Signs
Press F4 to turn Prices!A2:C6 into Prices!$A$2:$C$6. The dollar signs make the range absolute, so it will not shift when you copy the formula down.
Step 5: Add The Column Number And FALSE, Then Press Enter
Type 3, the third column, which holds the Unit Price. Type a comma, then FALSE, then the closing bracket, and press Enter. The complete formula is =VLOOKUP(B2,Prices!$A$2:$C$6,3,FALSE), and cell D2 should now show 59.00, the price of the mechanical keyboard with code P-103. Sheet names with a space need single quotes, like ‘Price List’!$A$2:$C$6.
Copying The Formula Down Without Breaking It
One correct formula is only half the job. Click cell D2 again, then double-click the small fill handle at its bottom-right corner, or drag it down to D5. Excel copies the formula into each row, and the relative lookup value adjusts automatically: D3 looks up B3, D4 looks up B4, D5 looks up B5. The locked range stays fixed on Prices!$A$2:$C$6.
The finished Orders sheet:
| Order ID | Product Code | Quantity | Unit Price |
|---|---|---|---|
| ORD-201 | P-103 | 2 | 59.00 |
| ORD-202 | P-101 | 1 | 24.99 |
| ORD-203 | P-105 | 3 | 49.99 |
| ORD-204 | P-102 | 1 | 39.50 |
If you add a product later, extend the range, for example to Prices!$A$2:$C$7.
Fixing #N/A And Other Common VLOOKUP Errors
Sooner or later a VLOOKUP will return an error instead of an answer — and the error itself tells you what went wrong.
#N/A Means Excel Could Not Find The Value
The #N/A error is the most common VLOOKUP result: the exact value was not found in the first column of the table. Before assuming the formula is broken, check the data — the usual causes are small: a trailing space after a product code, a code typed differently in the two sheets, or a number stored as text in one sheet and as a number in the other. Excel treats those as different values even though they look identical. Use TRIM to remove extra spaces and make sure both columns use the same data type. If a value is genuinely missing, add it to the Prices sheet. To show a friendlier message, wrap the formula in IFNA: =IFNA(VLOOKUP(B2,Prices!$A$2:$C$6,3,FALSE),”Price not listed”).
#REF! Means The Column Number Is Too Large
#REF! appears when the col_index_num is greater than the number of columns in your table_array. Our range Prices!$A$2:$C$6 has three columns, so asking for column 4 would produce #REF!. Count the columns inside your range, starting at 1. It also appears if you delete a column from the lookup table after writing the formula.
#VALUE! And #NAME? Mean The Formula Itself Has A Problem
#VALUE! usually means one argument is the wrong kind of value — for example, a col_index_num smaller than 1. #NAME? usually means Excel does not recognise something in the formula, most often a text value typed without quotes, such as =VLOOKUP(Fontana,B2:E7,2,FALSE) instead of =VLOOKUP(“Fontana”,B2:E7,2,FALSE). Using a cell reference, as this guide does, makes #NAME? much less likely.
Wrong Value Returned, With No Error At All
The trickiest problem is a VLOOKUP that returns the wrong value with no error at all. This almost always means the fourth argument was left out or set to TRUE: with an approximate match, Excel assumes the first column is sorted and returns the closest value below the one you asked for, which can look plausible while being completely wrong. If a price does not match the price list, first check that the formula ends in FALSE, then check that the lookup value is in the first column of the range.
Practical Tips And Limits To Keep In Mind
Three habits make VLOOKUP far more reliable: always use FALSE unless you need an approximate match, such as a graded pricing band; lock the table range with dollar signs; and keep lookup values clean — no leading or trailing spaces, consistent data types, no merged cells in the lookup table.
Know the limits too: VLOOKUP can only look to the right, never to the left of the lookup column, and it returns only the first match — if a code appears twice, you get the first price and never know about the second. Inserting a column inside the range shifts the column numbers and can silently change which column your formula returns.
A note for Excel in the browser: if Excel for the web starts behaving oddly — a stale result, or a shared link opening an old version — the cause is often the browser, not the spreadsheet. Learning how to clear your browser cache is a quick first step. To test how a shared link looks to someone else, it helps to know what incognito mode is and open it in a private window. And if you use the Excel app on your phone, large workbooks take up space — our guide to free up storage on your iPhone can help.
XLOOKUP: The Newer Alternative Worth Knowing
Microsoft now points VLOOKUP users toward XLOOKUP, an improved version that works in any direction and returns exact matches by default. Its syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]), and our price lookup becomes =XLOOKUP(B2,Prices!$A$2:$A$6,Prices!$C$2:$C$6). Instead of one table and a column number, XLOOKUP takes the search column and the return column as two separate ranges, so there is no column counting to get wrong, and it can return a value from the left of the lookup column. Full details are on Microsoft’s XLOOKUP function page.
The catch is availability: XLOOKUP is not available in Excel 2016 and Excel 2019. It is available in Microsoft 365, Excel 2021 and later, Excel for the web, and the Excel mobile apps. If everyone uses Microsoft 365 or a recent Excel, XLOOKUP is the better tool for new work; if the workbook might be opened on an older version, VLOOKUP remains the safe choice.
Conclusion
Using VLOOKUP between 2 sheets comes down to one pattern: =VLOOKUP(lookup_value, SheetName!range, column_number, FALSE). Point the lookup value at the cell holding the code, point the range at the table on the second sheet, count the columns from inside that range, and end with FALSE. Lock the range with dollar signs so the formula survives being copied down, and treat #N/A as a message about your data — check for extra spaces, mismatched data types, or a missing code. Try it with the Orders and Prices example: change a price on the Prices sheet and watch the Orders sheet update by itself.
Frequently Asked Questions
Can VLOOKUP Look Up Values On Another Sheet?
Yes. Put the sheet name and an exclamation mark before the range, like Prices!$A$2:$C$6. If the sheet name contains a space, wrap it in single quotes, like ‘Price List’!$A$2:$C$6. You can also click the other sheet’s tab and select the range while typing the formula, and Excel will write the reference for you.
Why Does My VLOOKUP Return #N/A When The Value Is Clearly There?
The value is usually not exactly the same in both places, even if it looks identical. Common causes are a trailing space, a number stored as text in one sheet and as a number in the other, or a different spelling. Use TRIM to remove extra spaces and make sure both columns use the same data type. If the value is genuinely missing from the table, add it to the source sheet.
Should I Use TRUE Or FALSE In VLOOKUP?
Use FALSE for an exact match in almost every case, especially when you are looking up codes, IDs or names. TRUE gives an approximate match and assumes the first column is sorted; leaving the argument out defaults to TRUE, which is the most common reason VLOOKUP returns a wrong value without any error.
Can VLOOKUP Return A Value From The Left Of The Lookup Column?
No. VLOOKUP only searches the first column of the range and only returns values from columns to its right. If the answer sits to the left of the lookup value, rearrange the columns or use INDEX and MATCH, or use XLOOKUP if your version of Excel supports it.
Is XLOOKUP Better Than VLOOKUP?
For new work in Microsoft 365 or Excel 2021 and later, yes — XLOOKUP defaults to an exact match, looks in any direction, and has built-in handling for missing values. VLOOKUP is still the right choice when a workbook may be opened in Excel 2019 or earlier, because XLOOKUP is not available in those versions and will show a #NAME? error there.
