Why does VLOOKUP fail when the numbers look identical?

The stored values differ beyond the decimals shown, either because of number formatting or floating-point remainders from a calculation. Round both sides before matching.

Does approximate match fix decimal mismatches?

Not reliably. Approximate match returns the nearest smaller value from a sorted list, which can pick the wrong row. Round both sides and use exact match instead.

How many decimal places should I round to?

To the precision that defines 'the same' for your data: two for currency, more for rates or measurements. Use the same number on both sides.

Is this a bug in Excel?

No. It's how binary floating-point numbers work in almost all software, including Google Sheets, Python and most databases.

Why a lookup fails on numbers with decimals

By the Analistable team · Updated 2026-10-10 · 5 min read

Excel and Google Sheets match the value stored in the cell, not the formatted value you see. A cell showing 12.35 may hold 12.345, and a calculated 0.3 may hold 0.30000000000000004. Round both sides to the precision that matters before matching — =XLOOKUP(ROUND(F2,2), ROUND(A2:A500,2), C2:C500) — or convert both to text with a fixed format.

Part of our guide: How to join spreadsheets on a common column

Two separate causes

When a lookup on a decimal key returns #N/A, or a join on an amount column leaves rows unmatched, the values almost always differ in a place you can't see. There are two ways that happens.

Display rounding. Number formatting changes what is shown, not what is stored. A unit price of 12.345 formatted to two decimals shows 12.35, while the other sheet holds a typed 12.35. They are different numbers.
Floating-point noise. Spreadsheets store numbers as binary floating point. Many decimals, such as 0.1, can't be represented exactly, so arithmetic leaves tiny remainders. A price calculated as cost × 1.2 can be stored a few billionths away from the value someone typed in the other file.

Both look identical on screen. Excel can hide some floating-point differences when it displays a result or compares in a simple formula, but exact-match lookups and joins in other tools are less forgiving, so don't rely on that.

How to see the real value

Select the cell and look at the formula bar: it shows the stored value to more places than the cell format.
Temporarily add decimals with Home → Increase Decimal, or format as Number with 15 decimal places.
Subtract the two values: =A2-Sheet2!A2. Anything other than exactly 0 explains the failed match, even if it's 1.8E-15.

Why four prices that look the same don't match
Shows	Stored in sheet A	Stored in sheet B	A − B	Cause
12.35	12.345	12.35	-0.005	Display rounding
0.30	0.30000000000000004	0.3	≈ 5.6E-17	Floating point (0.1 + 0.2)
24.00	23.999999999999996	24	≈ -3.6E-15	Calculated, not typed
7.50	7.5	7.5	0	Genuine match

The exact digits of floating-point remainders depend on the calculation. The point is not the size of the error but that any non-zero difference fails an exact match.

Fix 1: round both sides in the formula

Decide the precision that defines a match — pennies, so two places, for money; perhaps four places for exchange rates — and round both the lookup value and the lookup array. In Microsoft 365 or Excel 2021 XLOOKUP accepts a calculated array:

=XLOOKUP(ROUND(F2, 2), ROUND(Rates!A2:A500, 2), Rates!C2:C500, "no match")

In older Excel, use INDEX/MATCH, confirmed with Ctrl+Shift+Enter if your version needs it:

=INDEX(Rates!C2:C500, MATCH(ROUND(F2, 2), ROUND(Rates!A2:A500, 2), 0))

In Google Sheets, wrap the rounded range in ARRAYFORMULA if the function doesn't evaluate it as an array on its own. Round both sides, not just one: rounding the lookup value to 24 does nothing if the cell it should match still holds 23.999999999999996.

Fix 2: round the stored values with a helper column

If you'll match the same columns repeatedly, add =ROUND(A2, 2) next to the key on both sheets, paste as values, and look up the helper columns. That's faster on big ranges than rounding inside every formula, and it makes the matching rule visible to the next person.

Excel also has File → Options → Advanced → Set precision as displayed, which permanently cuts stored values to what the format shows across the whole workbook. It can't be undone and affects every number, so use it only on a copy and only if you understand the consequences.

Fix 3: convert to text with a fixed format

Where the number is really an identifier — a version like 2.10, a product code like 3.5 — compare text instead. =TEXT(A2, "0.00") turns 12.345 into "12.35" and 12.35 into "12.35", so both sides become the same string. Note that 2.1 and 2.10 are the same number, so if they mean different versions the column should have been text from the start; fix the import rather than the lookup.

The same problem in SQL and pandas

In SQL, round both sides in the join condition, or cast to a fixed-point decimal type:

SELECT o.order_id, o.amount, p.payout_id
FROM orders o
JOIN payouts p
  ON round(o.amount, 2) = round(p.amount, 2)
 AND o.order_date = p.payout_date;

CAST(amount AS DECIMAL(18,2)) does the same job and stores exact pennies. In pandas, df['amount'].round(2) before merge. A safer pattern for amounts is to compare within a tolerance — abs(a - b) < 0.005 — when the join has another key such as a date or reference; matching on amount alone risks pairing unrelated rows that happen to cost the same.

Analistable reads your sheets into DuckDB in the browser and writes the join as SQL, so you can ask it to match amounts to two decimal places and check the round() in the generated query. Amount matching comes up a lot in reconciliations — see matching Stripe payments to invoices.

Worked example: matching payouts to orders by amount and date

Orders (amount calculated as net × 1.2) vs bank lines (typed)
Date	Order amount (stored)	Bank amount	Exact match	Rounded match
2026-09-01	23.999999999999996	24.00	No	Yes
2026-09-01	59.94	59.94	Yes	Yes
2026-09-02	14.394	14.39	No	Yes
2026-09-02	102.00	—	No	No (not paid)

Rounded to pennies, three of four orders match and the fourth is a genuine gap. Without rounding only one matches and you'd chase two false exceptions. For a full list of reasons lookups fail, see VLOOKUP returns #N/A across sheets; for choosing the function, VLOOKUP vs XLOOKUP vs INDEX MATCH. The join spreadsheets guide has the bigger picture.

Frequently asked questions

Why does VLOOKUP fail when the numbers look identical?: The stored values differ beyond the decimals shown, either because of number formatting or floating-point remainders from a calculation. Round both sides before matching.
Does approximate match fix decimal mismatches?: Not reliably. Approximate match returns the nearest smaller value from a sorted list, which can pick the wrong row. Round both sides and use exact match instead.
How many decimal places should I round to?: To the precision that defines 'the same' for your data: two for currency, more for rates or measurements. Use the same number on both sides.
Is this a bug in Excel?: No. It's how binary floating-point numbers work in almost all software, including Google Sheets, Python and most databases.