To use VLOOKUP between two sheets in Excel, enter a formula on the sheet where you want the answer and point its table_array argument to the lookup table on the other sheet. For example, if the value to find is in A2 and the other sheet is named Product Catalog, use =VLOOKUP(A2,'Product Catalog'!$A$2:$C$100,3,FALSE) to return a matching value from the third column of that sheet’s table.
Prepare the two sheets
VLOOKUP is useful when one sheet contains values to look up and another sheet holds the related information. A common case is an order list on one worksheet and a product catalog on another. The order list might contain product codes, while the catalog contains product codes, descriptions, and prices. The formula matches each order’s code to the catalog and returns the requested detail.
Before writing a formula, check that the lookup key is present in both sheets and that the source table is organized for VLOOKUP:
- Put the values you want to search for in the leftmost column of the lookup table.
- Place the result you want returned in a column to the right of that key column.
- Keep each row’s related information together, with one record per row.
- Check that the keys use compatible values, such as text codes on both sheets or numeric IDs on both sheets.
The sheets can be in the same workbook; they do not need to be next to each other in the tab bar. For clarity, you can rename tabs by double-clicking a sheet tab and typing a descriptive name. A name containing spaces, such as Product Catalog, needs single quotation marks around it in a sheet reference. Excel usually adds those marks when you select the range with the mouse.
Build the cross-sheet formula
Suppose the current worksheet, Orders, has a product code in A2. On Product Catalog, column A contains product codes, column B contains product names, and column C contains prices. With those columns in rows 2 through 100, enter this formula in the cell where you want the price to appear:
With the lookup code in Orders!A2 and the catalog in Product Catalog!A2:C100, the formula returns the price from column C for the matching code:
=VLOOKUP(A2,'Product Catalog'!$A$2:$C$100,3,FALSE)
If A2 contains P-104 and the matching row on Product Catalog has 24.50 in column C, the result is 24.50. The formula uses a fixed source range, so it can be copied down for other order rows without the catalog range shifting. Select the result cell, enter the formula in the formula bar or directly in the cell, and press Enter. In Excel for Microsoft 365 on Windows, you can also start typing the formula and select the suggested function from the formula autocomplete list.
To fill the formula down, select the cell containing it and drag the small square at the lower-right corner (the fill handle) down the column. You can also copy and paste it. The A2 reference adjusts to A3, A4, and so on, while the dollar signs keep the catalog range fixed. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. Excel for the web uses the same formula syntax, though some interface details can differ.
Understand each part of VLOOKUP
VLOOKUP has four arguments in this order: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The brackets around range_lookup in Excel’s function description indicate an optional argument; they are not typed into the formula.
- lookup_value: The value to find, such as A2 on the Orders sheet.
- table_array: The full source range. For a different worksheet, include its name, an exclamation mark, and the range, as in ‘Product Catalog’!$A$2:$C$100.
- col_index_num: The number of the return column counted from the left edge of table_array. In A:C, A is 1, B is 2, and C is 3.
- range_lookup: Use FALSE for an exact match. TRUE or omitting this argument requests an approximate match, which has specific sorting requirements and is usually not what you want for product codes or IDs.
The column number is relative to the selected table, not the worksheet’s column letter. If the table starts at column D, its first column is still index 1. Also, the formula searches only the leftmost column of the specified range. If the key is in column B and the value you want is in column A, VLOOKUP cannot return that value with that layout; rearrange or select a suitable table, or use another lookup method.
Use exact match for IDs and codes
For most cross-sheet lookups involving names, product codes, employee IDs, or invoice numbers, use FALSE as the fourth argument. It tells Excel to find the same lookup value rather than a nearby value. Exact match does not mean Excel ignores data differences: a code stored as text may not match a number stored as a numeric value, and hidden spaces or inconsistent spelling can also prevent a match.
Approximate matching can be appropriate for a sorted threshold table, such as tax bands or grading boundaries, when the intended rule is to return the nearest lower threshold. In that case, use TRUE deliberately and make sure the first column is sorted in ascending order. If the key is a unique identifier and you are unsure which mode to use, FALSE is the safer choice. Do not rely on approximate matching for unsorted product or customer lists.
Make the formula easier to maintain
Dollar signs in $A$2:$C$100 make both ends of the source range absolute. Without them, Excel changes the range as you copy the formula down, which can cause later rows to search an unintended section of the catalog. You can add the dollar signs by typing them or by selecting a reference in the formula and pressing F4 on Windows. The equivalent shortcut on Mac can vary by keyboard and settings; Fn+F4 may be needed on some Macs. You can also type the dollar signs manually.
If the source list grows, a fixed end row such as 100 may eventually exclude new records. One option is to make the catalog an Excel table: select a cell in the data and choose Insert > Table, then confirm whether the table has headers. Tables expand as records are added. A table-based formula can be easier to read, but its exact structured-reference syntax depends on the table and column names; a normal sheet range is a simple starting point for learning VLOOKUP.
Use descriptive worksheet names, keep the lookup key column consistent, and avoid inserting unrelated data into the source range. If a column is inserted within the selected table, the numeric return-column index may no longer point to the intended field. Recheck the formula after changing the catalog’s column layout.
Fix common VLOOKUP problems
When the formula does not return the expected result, first check the exact source range and the value in its leftmost column. Then consider these common causes:
- #N/A: No matching value was found. Check for extra spaces, misspellings, different number-versus-text storage, or a source range that does not include the record.
- #REF!: The column index is larger than the number of columns in table_array, or a referenced column or range has been removed.
- Wrong result: The formula may use TRUE or omit the final argument, causing approximate matching. Use FALSE for an exact match.
- Unexpected column returned: Count the return column from the left edge of table_array, not from column A of the worksheet.
- Formula displayed as text: The cell may be formatted as Text, or the entry may start with an apostrophe. Change the cell format to General, then re-enter the formula.
- Formula will not calculate: Check whether Excel’s calculation mode is set to Manual. On Windows, review Formulas > Calculation Options and select Automatic if that is appropriate for the workbook.
If you want a friendlier message when there is no match, you can wrap the lookup in IFERROR. With the same Orders!A2 lookup value and Product Catalog!A2:C100 source table, this formula displays Not found if the lookup produces an error:
=IFERROR(VLOOKUP(A2,'Product Catalog'!$A$2:$C$100,3,FALSE),"Not found")
Use IFERROR thoughtfully: it replaces any error from the expression, not just a missing match. During troubleshooting, leaving the original error visible can help reveal a broken range or an invalid column index. If you only want to handle missing matches, newer Excel versions also have IFNA, which catches #N/A specifically.
VLOOKUP compared with newer lookup options
VLOOKUP remains widely understood and works in many older Excel versions, which can matter when sharing a workbook. Its main limitations are its left-to-right structure and its numeric return-column index. If someone inserts a column into the selected range, the index may need to be updated.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It separates the lookup range from the return range, can look in either direction, and uses exact match by default. In an up-to-date Excel version, the equivalent price lookup is:
With the lookup code in A2, codes in Product Catalog!A2:A100, and prices in Product Catalog!C2:C100, XLOOKUP returns the price for an exact match:
=XLOOKUP(A2,'Product Catalog'!$A$2:$A$100,'Product Catalog'!$C$2:$C$100,"Not found")
If an older version of Excel must open the workbook, confirm that its users can work with XLOOKUP before replacing VLOOKUP; unsupported functions may display as an unrecognized-function error. INDEX and MATCH are another established alternative that can look left or right, but they require a more involved formula. For a simple lookup where the key is the first source column and the result is to its right, VLOOKUP is often straightforward.
Lookups between workbooks
A lookup across two worksheets in the same workbook is different from a lookup across separate workbook files. If the source is in another workbook, the table reference includes the workbook name as well as the sheet name, and Excel may update that external reference when the source file is moved or renamed. Keep both workbooks accessible while creating and checking the link. If the data is used regularly, importing or combining it into one workbook can make the setup easier to maintain.
For a same-workbook formula, the reference ‘Product Catalog’!$A$2:$C$100 points to a sheet tab. It does not require a file path. If you are unsure whether two tabs belong to the same file, check the workbook title bar or use View > Switch Windows on Windows to see open workbook windows. External references can be valid, but a broken path can prevent Excel from finding the source data.
Frequently Asked Questions
Can VLOOKUP search another sheet in the same workbook?
Yes. Put the sheet name and an exclamation mark before the source range, as in ‘Product Catalog’!$A$2:$C$100. Use single quotation marks around sheet names that contain spaces or punctuation.
Why does VLOOKUP return #N/A even though the value looks the same?
The values may differ in a way that is not obvious, such as a leading or trailing space, text stored on one sheet and a number stored on the other, or a spelling difference. Also check that the lookup key is in the first column of table_array and that the source range includes the relevant row.
Does the VLOOKUP column number count from A?
No. It counts from the left edge of table_array. If the selected range is B2:D100, B is column index 1, C is 2, and D is 3.
Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is generally right for IDs, codes, and names. Use TRUE only when you specifically need an approximate match and the first source column is sorted in ascending order.
Can VLOOKUP return a value to the left of the lookup column?
No. VLOOKUP searches the first column of its table_array and returns a result from a column to its right. If you need to return a value to the left, use XLOOKUP in a supported Excel version, or consider INDEX and MATCH.
Quick checklist
For a reliable cross-sheet lookup, keep the key in the source table’s first column, select a range that includes the desired return field, count the return column from the range’s left edge, and use FALSE for an exact match. Lock the source range before filling the formula down, and test a known code whose expected result you can verify in the catalog. Those checks catch most setup errors before the workbook is shared.
Related
- To Do List Sample Excel: Create a Practical Task Tracker
- To-Do List Template in Excel: Create and Use Yours
- To Do List Excel Sheet Template: Make a Useful Tracker
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- If Excel Contains Specific Text: Formulas That WorkTo check whether a cell contains specific text in Excel, use IF with SEARCH and ISNUMBER. With the…
- Excel Workday Difference Between Two DatesIn Excel for Microsoft 365 on Windows, use =NETWORKDAYS(A2,B2) to count working days from the start date in…
- Excel Column Addition Formula: Sum a ColumnTo add every number in an Excel column, use =SUM(B:B), replacing B with the column you want to…
- Excel Formula to Randomly Select From a ListWith names in A2:A20, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))) to return one randomly chosen entry. The formula selects a row position…
- Excel Formula Keyboard Shortcuts and How to Use ThemThe most useful Excel formula shortcuts on Windows are Alt+= to insert AutoSum, F2 to edit a cell,…
- Excel SUM Without Hidden Cells: Which Formula to UseTo sum only visible rows in Excel, use SUBTOTAL with function number 109. With amounts in B2:B6 and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.