To return a value from one Excel sheet based on a match in another, enter a VLOOKUP formula on the sheet where you want the result. For example, if the ID to search for is in A2 on your current sheet and the matching IDs and names are in columns A and B of a sheet named Data, use =VLOOKUP(A2,Data!$A$2:$B$100,2,FALSE). Replace the sheet name, cell, and range to match your workbook. This guide shows how to build the formula, copy it down, and handle common errors in Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with two worksheets: one containing the value to look up and one containing the reference data. This method works when both sheets are in the same workbook.
- A shared identifier on both sheets, such as a product code, customer ID, or employee number. The values should use the same format on both sheets.
- A clear idea of which column contains the identifiers and which column contains the result you want to bring across.
- Permission to edit the workbook. Save a copy first if the file is important, especially before changing or filling formulas across a large range.
Step-by-Step
- Check how the two sheets are organized. Open the workbook and look at the sheet tabs at the bottom. In this example, the active sheet is the one that needs the result, and the source sheet is named Data. On Data, column A contains product IDs and column B contains product names. On the active sheet, column A contains the product ID to search for, and column B is where the product name should appear. VLOOKUP requires the lookup IDs to be in the leftmost column of the range you select; it returns data from a column to the right.
- Make sure the lookup values can match. Compare a few IDs on both sheets. A number stored as text may not match the same digits stored as a number, and extra spaces can also prevent a match. Check that IDs are complete and consistent, including any leading zeros. For example, codes such as 00427 may need to remain text so that Excel does not treat them as the number 427. Do not remove zeros if they are meaningful parts of the identifier.
- Select the cell for the first result. On the sheet where you want the returned value, click the first empty cell beside the first lookup value. In the example layout, if the ID to search for is in A2, click B2. Starting beside the first data row makes it easier to copy the formula down later. Avoid putting the formula in the lookup column itself, because that can overwrite the value it needs to search for.
- Enter the VLOOKUP formula. With the lookup value in A2 on the current sheet, and IDs in Data!A2:A100 with names in Data!B2:B100, enter
=VLOOKUP(A2,Data!$A$2:$B$100,2,FALSE)in B2. The first argument, A2, is the value to find. The second argument is the two-column source range. The number 2 tells Excel to return the value from the second column of that range, and FALSE requests an exact match. Press Enter. If A2 contains product ID P-1042 and Data has P-1042 paired with Travel Mug, B2 should show Travel Mug. - Build the reference by selecting the source range if needed. You do not have to type the sheet reference from memory. Start entering the formula in B2, type the opening part and select A2 as the lookup value. Then click the Data sheet tab and drag across the source columns and rows. Finish the formula with the return-column number and exact-match setting. Excel inserts the worksheet reference. Press Enter to complete it. This is especially helpful when a tab name contains spaces or punctuation, because Excel adds the needed quotation marks around the sheet name.
- Keep the source range fixed when copying. The dollar signs in the example range lock its row and column references. The lookup reference A2 is not locked, so it will change to A3, A4, and so on as you copy the formula down. Without the dollar signs, the source range would shift too, which can cause later rows to search the wrong cells. If you use a different source range, keep the dollar signs around that range unless you intentionally want it to move.
- Fill the formula down the result column. Click B2 again. Point to the small square at the lower-right corner of the selected cell, called the fill handle, and drag it down alongside the remaining lookup values. You can also copy B2, select the cells below it, and paste. Check a few rows in the middle and at the bottom: the lookup cell should change for each row, while the source range should stay the same. If the adjacent data is continuous, double-clicking the fill handle can fill down to the end of that neighboring data.
- Confirm that the results are sensible. Compare a few returned values with the source sheet, including one near the bottom of the range. If you see an error, check that the lookup value exists in the selected source range, the IDs are in the first column of that range, and the return-column number points to the intended field. Also verify that the source range includes every data row. A formula can calculate without an error and still return the wrong field if the column number is incorrect.
If the source tab has a space in its name, Excel encloses it in single quotation marks in the sheet reference. With lookup IDs in A2:A100 and names in B2:B100 on a tab named Sales Data, and the lookup ID in A2 on the active sheet, the formula for B2 is =VLOOKUP(A2,'Sales Data'!$A$2:$B$100,2,FALSE). Keep the apostrophes and exclamation mark as shown when typing the reference yourself.
Common Mistakes
- Leaving out exact match. Using TRUE or omitting the final argument can make VLOOKUP use approximate matching. That behavior is not suitable for most ID lookups, and it may require the source keys to be sorted. Use FALSE when you need an exact ID, name, or code match.
- Putting the lookup column somewhere other than the left edge. If your source range begins with the name column and the ID is in the next column, VLOOKUP cannot search the ID and return a value to its left. Select a range that starts with the ID column, or rearrange the data. VLOOKUP only returns from columns to the right of its lookup column.
- Using the wrong return-column number. The number counts columns within the selected range, not worksheet column letters. If the range is C:E, column C is 1, D is 2, and E is 3. Choosing 2 when you meant 3 returns the wrong field.
- Letting the source range move. If you copy the formula down without locking the source range, its row references may shift. Use dollar signs around the source range and test a copied formula before filling hundreds of rows.
- Including headers or omitting records. Including a header row is not always harmful, but it can confuse your range selection and return-column count. More importantly, a range that stops before the last record cannot find values below it. Select the actual data columns and include all relevant rows.
Tips
- Use distinctive identifiers rather than names when possible. Two people may share a name, but a unique employee ID is less likely to return an unintended match.
- Keep the lookup and result columns clearly labeled. A heading such as Product Name above the returned values helps you and other workbook users understand what the formula provides.
- When you add source records regularly, consider converting the source range to an Excel Table with Insert > Table. Tables expand as you add rows. If you replace the ordinary range in the formula with table references, use the exact table and column names shown in your workbook and check the result after editing.
- For a formula that should show a helpful message instead of an error, with lookup values in A2 on the active sheet and IDs and names in Data!A2:B100, enter
=IFNA(VLOOKUP(A2,Data!$A$2:$B$100,2,FALSE),"Not found")in B2. This displays Not found when no exact match exists; it does not fix a mismatched ID or an incorrect range, so investigate repeated missing results. - To rename a worksheet, double-click its tab, type the new name, and press Enter. Excel updates references to that sheet in formulas in the workbook.
Troubleshooting
You see #N/A: Excel has not found an exact match. Confirm that the lookup value really appears in the source column and that your range includes its row. Check for leading or trailing spaces and for numbers stored as text on one sheet but as numbers on the other. If these values come from pasted data, inspect a few cells directly before changing the formula. Do not switch to approximate matching just to hide #N/A; that can return an incorrect record.
You see #REF!: The return-column number is greater than the number of columns in the selected range, or a referenced source column may have been deleted. For a two-column source range, the return-column number can only be 1 or 2. Check the range and the column number together.
You see #NAME?: Check the spelling of VLOOKUP and the sheet name. When typing a sheet reference with spaces, surround the tab name with single quotation marks, followed by an exclamation mark. If you built the formula by selecting the range, inspect the formula bar for a reference to the intended tab.
The same answer appears on every row: Look at the lookup reference in each filled formula. If it is locked as $A$2, every row searches A2. For a down-filled list, the lookup reference normally needs to move from A2 to A3 and onward, while the source range remains locked. Conversely, if the source range changes between rows, add dollar signs to that range.
The formula appears as text instead of calculating: The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, edit the formula, and press Enter. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options and confirm that calculation is set to Automatic if results are not updating. On Mac, the worksheet formulas are the same; ribbon placement and menus can differ slightly. Excel for the web also supports VLOOKUP, though its ribbon layout differs from the Windows desktop app.
Frequently Asked Questions
Can VLOOKUP search between two different sheets in the same workbook?
Yes. Put the lookup formula on one sheet and refer to the source range on the other sheet using the sheet name, an exclamation mark, and the range. The tabs do not need to be adjacent.
Does the source sheet have to be open?
No, not when both sheets are in the same workbook. Both worksheets are part of the workbook, so the formula can refer to the source sheet even when another tab is active.
Can VLOOKUP return a value to the left of the lookup column?
No. VLOOKUP searches the first column of its selected range and returns a value from a column to its right. Put the lookup column at the left edge of the range or choose another lookup approach if the source layout cannot change.
Why does VLOOKUP return the wrong result even though it finds a match?
Check that the final argument is FALSE and that the return-column number identifies the intended column within the selected range. Also check for duplicate IDs: VLOOKUP returns the first matching record it encounters, so repeated keys can make a result look unexpected.
Can I use this formula between separate Excel files?
Yes, but the reference must point to the other workbook as well as its worksheet, and Excel may ask whether to update external links. For a first attempt, keep both workbooks open while selecting the source range. Confirm that the link points to the correct file and sheet before relying on results.
Is VLOOKUP the same on Mac and Excel for the web?
The function syntax and the core formula are the same in Microsoft Excel on Windows, Mac, and the web. The ribbon layout and some navigation details differ, so use the formula bar and worksheet tabs if a Windows menu path is not in the same place.
Related
- How to Put Color in an Excel Cell: A Practical Guide
- How to Put Borders in Excel Cells: Clear Steps and Tips
- How to Put Strikethrough in Excel with Shortcuts and Menus
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Group the Same Numbers in Excel: Sort and CountTo group identical numbers in Excel, sort the entire data range by the column containing those numbers: select…
- Excel Date Difference in Years: Formulas That WorkTo find the number of complete years between two dates in Excel, use =DATEDIF(A2,B2,"Y"), with the earlier date…
- How to Insert a Filter in Excel: Quick StepsTo insert a filter in Excel, click a cell in your data, then select Data > Filter. Excel…
- Text Filter in Excel With Multiple Criteria ExplainedTo filter text in Excel by multiple criteria, select your data, choose Data > Filter, open a column’s…
- Excel OFFSET Function: How to Use It in Formulas and RangesIn Excel for Microsoft 365 on Windows, OFFSET returns a reference moved a chosen number of rows and…
- VLOOKUP col_index_num: How to Choose the Right NumberIn Excel’s VLOOKUP function, col_index_num is the number of the column to return from the selected table range,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.