Matching data between two sheets in the same Excel file is simpler than matching across two separate files, since there's no external file path involved - just a sheet name added to the front of the range reference.
This page covers that setup using a warehouse inventory sheet and a separate reorder-tracking sheet.
Sheet "Inventory" contains the full stock table:
| Item Code | Item Name | Warehouse | Stock Qty |
|---|---|---|---|
| ITM-01 | Steel Bolts | WH-A | 1200 |
| ITM-02 | Rubber Gasket | WH-B | 450 |
| ITM-03 | Copper Wire | WH-A | 980 |
A second sheet, "Reorder", lists Item Codes that need restocking and needs Stock Qty pulled in next to each one.
On the Reorder sheet, next to an Item Code in A2:
The exclamation mark after the sheet name is what tells Excel the range belongs to a different sheet rather than the current one - Inventory!A2:D4 means "A2:D4 on the sheet named Inventory," not a named range called Inventory.
If the sheet were named "Stock Inventory" instead, the reference needs single quotes around the sheet name: 'Stock Inventory'!A2:D4 - Excel adds these automatically when the range is selected by clicking, but they need to be added manually if the formula is typed from scratch.
Turbo Excel VLookup can match two sheets within a single workbook the same way it matches two files - both are just loaded as tables, with the matching Item Code columns selected from dropdowns rather than typed as a formula reference.
When a VLOOKUP formula that used to work suddenly returns the wrong value with no visible error, the most likely cause is that a column was inserted or deleted inside table_array, shifting col_index_num out of alignment with the intended column.
Selecting the formula and pressing F9 with just that part of the formula highlighted shows the calculated value inline, which is a quick way to confirm whether table_array is still pointing where it should.
A common source of confusing errors is editing the same table that a VLOOKUP formula is pulling from, especially when rows are sorted or filtered mid-edit. Keeping a clearly labeled, rarely edited "source" sheet that formulas pull from, separate from a "working" sheet where day-to-day changes happen, reduces the chance of a lookup silently pulling from a half-edited row.
What symbol connects a sheet name to a cell range in a formula?
An exclamation mark, such as Inventory!A2:D4, which tells Excel the range belongs to a different sheet.
Do I need quotes around a sheet name in a formula?
Only if the sheet name contains spaces or special characters, in which case single quotes are required.
Can I click into the other sheet instead of typing its name?
Yes, clicking the sheet tab and selecting the range while building the formula inserts the correct reference automatically.
What happens if I rename the Inventory sheet later?
Excel automatically updates any formulas referencing it by its old name, as long as the rename happens within the same file.
Does locking the range matter for a two-sheet formula?
Yes, locking with F4 keeps the Inventory reference fixed when the formula is copied down the Reorder sheet.
Is there a way to match two sheets without this formula syntax?
Yes, Turbo Excel VLookup can treat two sheets in one workbook as two tables to match through its dropdown interface.
What's a fast way to check what a formula is actually returning?
Selecting part of the formula in the formula bar and pressing F9 shows its calculated value directly, without changing the formula.
Can trailing spaces in a cell cause a lookup to fail?
Yes, an extra space at the end of a value is invisible but prevents an exact match; TRIM() around the lookup value often fixes this.
Does the order of rows in the source data matter for an exact match?
No, an exact match search works regardless of row order; only an approximate match requires sorted data.
Is it safe to have duplicate values in the lookup column?
A lookup formula will only return the first matching row it finds, so duplicates can silently return the wrong record if not accounted for.
Ready to stop fighting #N/A errors and match your spreadsheets visually?