Compare & combine files · 5 min read

VLOOKUP Between Two Sheets in Excel: Exact Matches and Missing IDs

You have an order list on one worksheet and prices on another. The rows are in different orders, so copying the price column would attach prices to the wrong products. A lookup connects the two lists through a shared product ID.

JHSOFT editorialUpdated September 9, 2026

WORKED EXAMPLESynthetic data

Price List!A1:C6
product_id	item	price
P101	Lamp	18
P102	Tray	0
P103	Hook	7
P103	Hook	8
P104	Stand	[empty]

Orders!A1:A7
product_id
P101
P103
P999
[empty]
P102
P104

Orders!B2:B7 returns 18, 7, Not found, an empty result, 0, and 0. The second result needs duplicate review: P103 has prices 7 and 8. The final zero is a blank source price being returned as zero; it is not evidence of a free product.

Download price-list.csv ↓Download order-ids.csv ↓

Set up the two worksheets

This example works in desktop Excel 2016 and later, including Microsoft 365. It uses ordinary worksheet formulas, so XLOOKUP is not required. Formula separators shown here are commas; an installation that uses semicolons needs those separators instead.

Create a fresh workbook with sheets named Orders and Price List. Enter the sample headers and values in the indicated cells, leaving the marked cells genuinely empty. Do not type the bracketed placeholder. You may also use the two CSV samples as input, but put the data in the named worksheets before entering the formulas.

On Price List, put product_id, item, and price in A1:C1; enter the five source records in rows 2–6.
On Orders, put product_id in A1; enter the six lookup rows in A2:A7, including the empty A5.
Add price_result in Orders!B1 and source_matches in Orders!C1. Keep source IDs as text and source prices numeric.

Enter the exact-match formula and fill down

Enter the formula below in Orders!B2 and fill it through B7. The outer IF leaves the deliberately empty order ID alone. IFNA replaces a missing-match error with a readable label; it does not suppress every kind of formula error.

The dollar signs keep the source rectangle fixed as the formula moves down. A2 becomes A3, A4, and so on, while the search range remains unchanged. Single quotation marks surround Price List because the sheet name contains a space. You can select the source cells while building the formula to let Excel insert the reference.

Enter in Orders!B2, then fill through B7

=IF(A2="","",IFNA(VLOOKUP(A2,'Price List'!$A$2:$C$6,3,FALSE),"Not found"))

The order IDs locate source prices despite different row positions, and P999 has no match. — Illustrated example. Synthetic lookup example. The P103 result is provisional until duplicate review.

Read the four arguments correctly

The return column number is counted inside the selected rectangle. Here A is column 1, B is column 2, and C is column 3. If your real range starts in D, column 3 means F. It does not mean worksheet column C.

The ID must occupy the leftmost column of that rectangle. FALSE prevents approximate matching, and the source does not need to be sorted for this example. Keep FALSE visible when adapting the formula: omitting that argument changes the matching behavior.

Argument	This workbook	Purpose
lookup_value	A2	ID from the current order row
table_array	'Price List'!$A$2:$C$6	Source ID and return columns
col_index_num	3	Return price
range_lookup	FALSE	Require an exact match

Related help: Match a record using two criteria

Count source matches before using the prices

Enter the count formula in C2 and fill through C7. The expected counts are 1, 2, 0, empty, 1, and 1. A successful lookup and a unique lookup are different checks: B3 contains a number even though C3 correctly reports two candidate rows.

For P103, inspect both source records and decide whether they are obsolete versions, different variants, or an actual duplicate. Add another matching field when the ID alone is insufficient. Do not choose the first row merely because the formula happened to return it; changing source order could change the answer.

Enter in Orders!C2, then fill through C7

=IF(A2="","",SUMPRODUCT(--('Price List'!$A$2:$A$6=A2)))

Separate missing IDs, blank prices, and real zeros

P999 is absent from the source, so it returns Not found. P102 exists and has a recorded price of zero. P104 also exists, but its price cell is empty; a direct VLOOKUP return displays that empty cell as zero. Looking only at column B would hide this distinction.

Filter Price List for empty prices and resolve those records before using the joined prices in calculations. Also inspect existing error values in the source. IFNA can catch a returned #N/A error as well as a missing lookup, so the match count and source review provide context that the display label alone cannot.

A real zero and an empty return cell both appear as zero in a direct VLOOKUP result. — Illustrated example. Synthetic example. Inspect the source price before interpreting a returned zero.

Check exact-match limits and refresh the range

These sample IDs contain letters and digits. VLOOKUP text matching is not case-sensitive, and FALSE still permits wildcard syntax in a text lookup value. If literal IDs can contain an asterisk or question mark, escape wildcard characters or use a comparison method designed for literal text. Do not assume FALSE means a case-sensitive character comparison.

Keep identifier types consistent on both sheets, and investigate unexpected spaces rather than stripping characters indiscriminately. When new source rows arrive, expand the bounded source and audit ranges together or convert the source to an Excel Table and adapt both formulas. Before sending a snapshot, copy the reviewed result and use Paste Special > Values in a separate output sheet.

Common questions

Why does the formula show #REF!?

Check that the return column number fits inside the source rectangle and that the referenced sheet still exists. A number of 3 needs at least three source columns. IFNA intentionally leaves this structural error visible.

Can VLOOKUP return every match from the other sheet?

No. This formula returns one matching row. Resolve duplicate source IDs for a one-to-one lookup, or use FILTER in a supported Excel version when you need all matching records.

Can I use a source in another workbook?

Yes, but the reference must also name that workbook and creates an external link. This walkthrough keeps both sheets in one workbook. For a portable delivery, retain the source or save a reviewed values-only snapshot.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →