VLOOKUP in Excel: the syntax, and the five ways it breaks on a ledger

VLOOKUP looks for a value in the first column of a table and returns the value from the same row in a column you give by number. Always end it with FALSE: without it Excel uses approximate match, which can return another customer's answer with no error. On a ledger, that and four other failures are where the wrong numbers come from.

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

The syntax

range_lookup is optional, and TRUE is the default. Microsoft's VLOOKUP page says approximate match assumes the first column is sorted, and asks you to sort it before using TRUE.

A working example: segment for each invoice

The Ledger sheet has one row per invoice:

The Customers sheet is the master, sorted by ID:

In Ledger!D2, filled down to D7:

=VLOOKUP(B2,Customers!$A$2:$D$6,3,FALSE)

Segment is the third column of A:D, so col_index_num is 3. The $ signs keep the table fixed as the formula is filled down. The six results are Enterprise, Mid-market, Enterprise, #N/A, Mid-market, Enterprise. C-1099 is not in the master, and #N/A is the honest answer. Summed by segment: Enterprise 28,360, Mid-market 8,650, unmatched 1,980.

The range $A$2:$D$6 covers five customers. Extend it as the master grows, or convert the master to an Excel Table so the reference grows by itself.

Failure 1: the default approximate match

Leave out FALSE:

=VLOOKUP(B2,Customers!$A$2:$D$6,3)

The five known customers give the same answers. C-1099 now returns Small. With approximate match, VLOOKUP returns the row with the largest ID less than or equal to the one it was given. In ascending order C-1005 is the last ID below C-1099, so INV-1004 is attributed to Ashby Engineering's segment. The Small segment shows $1,980 of sales it never made, and no error appears anywhere.

That result depends on the master being sorted ascending. On an unsorted master approximate match is unpredictable: Microsoft's #N/A guide warns that TRUE can return #N/A or erroneous results. Type FALSE every time.

Failure 2: a column is inserted

col_index_num is a number typed into the formula. Someone inserts a Region column after Name in the master: Segment moves from C to D. Excel widens the range in every formula to $A$2:$E$6, but the 3 stays a 3, and column 3 is now Region. The exact-match formula returns Midwest, Midwest, Northeast, #N/A, West, Midwest, and every report grouped by "segment" is now grouped by region.

Look up the column number from the header row instead:

=VLOOKUP(B2,Customers!$A$2:$D$6,MATCH("Segment",Customers!$A$1:$D$1,0),FALSE)

MATCH returns 3 today and 4 after the insert, because its range widens too.

Failure 3: spaces and numbers stored as text

Exact match means exactly. An export that writes "C-1004 " with a trailing space returns #N/A for INV-1002, and $3,150 joins the unmatched line. Keys that are plain numbers fail the same way when one table stores 1001 as a number and the other stores "1001" as text. Microsoft's page lists both: don't store numbers as text in the first column, and make sure it has no leading or trailing spaces.

A stopgap for the ledger side:

=VLOOKUP(TRIM(B2),Customers!$A$2:$D$6,3,FALSE)

The real fix is to clean the key once, at source, before any lookup; how to prepare a sales export for analysis covers spaces, text-numbers and the rest.

Failure 4: duplicates return the first match

After a system migration, Northgate Retail is added to the master again at row 7, still as C-1004 but now in the Enterprise segment. VLOOKUP stops at the first C-1004, in row 5, returns Mid-market, and never reads row 7. Nothing flags that the master holds two answers.

In Customers!E2, filled down to E7, with the range extended to the new row:

=COUNTIF(Customers!$A$2:$A$7,A2)>1

TRUE on rows 5 and 7. A master with one row per ID removes this failure; see how to build a customer master from scratch, and keep old and new IDs in an identifier map rather than as extra rows.

Failure 5: it cannot look left

To find the ID for "Kestrel Supplies", the name column is the one to search, but VLOOKUP only searches the first column of table_array. =VLOOKUP("Kestrel Supplies",Customers!$A$2:$D$6,1,FALSE) looks for the name among the IDs and returns #N/A. INDEX with MATCH, or XLOOKUP, returns C-1003; both are shown side by side in XLOOKUP vs VLOOKUP vs INDEX MATCH.

The check that catches all five

Two helper columns on the ledger and three numbers on a check sheet. In Ledger!D2, the segment with a visible label for misses:

=IFNA(VLOOKUP(B2,Customers!$A$2:$D$6,3,FALSE),"Not in master")

IFNA replaces only #N/A, so any other error still shows. In Ledger!E2, how many times the ID is in the master:

=COUNTIF(Customers!$A:$A,B2)

It counts the whole ID column, so a duplicate added below the lookup range is still seen. Then:

On the clean example: Enterprise 28,360, Mid-market 8,650, Small 0 and Not in master 1,980 sum to 38,990; one ID has no match and one row is labeled; no duplicates. Each failure trips at least one test:

1. Approximate match. The segment total still ties, because the 1,980 moved to Small instead of vanishing. The second test catches it: C-1099 has a match count of 0 but no row says "Not in master".
2. Inserted column. Regions replace segments, only the 1,980 lands on a listed label, and the first test shows −37,010.
3. Dirty keys. Match counts of 0 rise, and the unmatched line grows from 1,980 to 5,130.
4. Duplicates. The third test returns 1.
5. Looking left. A VLOOKUP that searches the ID column for names finds none of them, so every row is unmatched and the unmatched line is the whole ledger.

A segment report that passes all three can be sent. This is the same control-total habit as five checks before you trust a spreadsheet total.

Where it goes wrong

• Omitting FALSE returns a plausible wrong answer instead of #N/A (failure 1).
• A hard-coded column number stops pointing at the right field when the master changes (failure 2).
• IFERROR round everything turns a missing customer, and a broken formula, into a blank that is summed as nothing. Use IFNA with a label.
• Whole-column lookups such as A:D on large workbooks recalculate slowly. Convert the master to a Table and look up the Table.

When to use XLOOKUP instead

If everyone who opens the file has Microsoft 365 or Excel 2021 or later, XLOOKUP removes failures 1, 2 and 5 by design: exact match is the default, the return column is a range rather than a number, and it can return a column to the left. It does nothing about failures 3 and 4, so the checks still apply. The full comparison, with INDEX MATCH for files that must open in older Excel, is in XLOOKUP vs VLOOKUP vs INDEX MATCH.

Matching IDs without guessing

Covirage matches ledger IDs to the master with exact rules, lists the unmatched and duplicated IDs, and reconciles the joined total to the ledger before any answer is written. Its deterministic tools do the matching and the arithmetic; the external AI model only explains the result. Upload the ledger and master as they come and see how it works for finance teams. If you are maintaining lookups across many files, read four signs a spreadsheet is no longer enough.

title: "VLOOKUP in Excel: the syntax, and the five ways it breaks on a ledger" description: "The VLOOKUP syntax and one working example on a six-invoice ledger, then the five ways it gives wrong answers on finance data: approximate match by default, inserted columns, dirty keys, duplicate IDs and left lookups. It ends with the checks that catch all five before a report goes out." seoTitle: "VLOOKUP in Excel, and why it breaks on ledgers" metaDescription: "VLOOKUP finds a value in a table's first column and returns a value from another column. Syntax, one example, and five ways it gives wrong answers." date: 2026-09-30 category: guides industry: finance-teams solution: excel-analysis keywords: ["vlookup", "vlookup excel", "vlookup formula", "how to do a vlookup", "vlookup not working", "vlookup returns wrong value", "vlookup n/a error"] answer: "VLOOKUP looks for a value in the first column of a table and returns the value in the same row from a column you number: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). Always give FALSE for an exact match; the default is approximate and can return a wrong row without an error. It cannot look left, and breaks when columns are inserted." faq:

• q: "Why is my VLOOKUP returning #N/A?" a: "The value was not found. The usual causes are a missing FALSE with unsorted data, trailing spaces, numbers stored as text in one table only, or a record that is genuinely missing from the lookup table. Check with =COUNTIF(first_column, value)."
• q: "Why does VLOOKUP return the wrong value?" a: "Usually because range_lookup was left out, so Excel used approximate match and returned the nearest lower key. It can also be a column number that no longer points at the intended column after a column was inserted."
• q: "Can VLOOKUP look to the left?" a: "No. VLOOKUP only searches the first column of the table and returns values to its right. To look left, use INDEX with MATCH, or XLOOKUP in Microsoft 365 and Excel 2021 or later."
• q: "What does FALSE mean in VLOOKUP?" a: "FALSE, or 0, as the fourth argument asks for an exact match. Without it Excel assumes TRUE, an approximate match that needs the first column sorted ascending and returns the largest value less than or equal to the lookup value."

VLOOKUP looks for a value in the first column of a table and returns the value from the same row in a column you give by number. Always end it with FALSE: without it Excel uses approximate match, which can return another customer's answer with no error. On a ledger, that and four other failures are where the wrong numbers come from.

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

The syntax

range_lookup is optional, and TRUE is the default. Microsoft's VLOOKUP page says approximate match assumes the first column is sorted, and asks you to sort it before using TRUE.

A working example: segment for each invoice

The Ledger sheet has one row per invoice:

The Customers sheet is the master, sorted by ID:

In Ledger!D2, filled down to D7:

=VLOOKUP(B2,Customers!$A$2:$D$6,3,FALSE)

Segment is the third column of A:D, so col_index_num is 3. The $ signs keep the table fixed as the formula is filled down. The six results are Enterprise, Mid-market, Enterprise, #N/A, Mid-market, Enterprise. C-1099 is not in the master, and #N/A is the honest answer. Summed by segment: Enterprise 28,360, Mid-market 8,650, unmatched 1,980.

The range $A$2:$D$6 covers five customers. Extend it as the master grows, or convert the master to an Excel Table so the reference grows by itself.

Failure 1: the default approximate match

Leave out FALSE:

=VLOOKUP(B2,Customers!$A$2:$D$6,3)

The five known customers give the same answers. C-1099 now returns Small. With approximate match, VLOOKUP returns the row with the largest ID less than or equal to the one it was given. In ascending order C-1005 is the last ID below C-1099, so INV-1004 is attributed to Ashby Engineering's segment. The Small segment shows $1,980 of sales it never made, and no error appears anywhere.

That result depends on the master being sorted ascending. On an unsorted master approximate match is unpredictable: Microsoft's #N/A guide warns that TRUE can return #N/A or erroneous results. Type FALSE every time.

Failure 2: a column is inserted

col_index_num is a number typed into the formula. Someone inserts a Region column after Name in the master: Segment moves from C to D. Excel widens the range in every formula to $A$2:$E$6, but the 3 stays a 3, and column 3 is now Region. The exact-match formula returns Midwest, Midwest, Northeast, #N/A, West, Midwest, and every report grouped by "segment" is now grouped by region.

Look up the column number from the header row instead:

=VLOOKUP(B2,Customers!$A$2:$D$6,MATCH("Segment",Customers!$A$1:$D$1,0),FALSE)

MATCH returns 3 today and 4 after the insert, because its range widens too.

Failure 3: spaces and numbers stored as text

Exact match means exactly. An export that writes "C-1004 " with a trailing space returns #N/A for INV-1002, and $3,150 joins the unmatched line. Keys that are plain numbers fail the same way when one table stores 1001 as a number and the other stores "1001" as text. Microsoft's page lists both: don't store numbers as text in the first column, and make sure it has no leading or trailing spaces.

A stopgap for the ledger side:

=VLOOKUP(TRIM(B2),Customers!$A$2:$D$6,3,FALSE)

The real fix is to clean the key once, at source, before any lookup; how to prepare a sales export for analysis covers spaces, text-numbers and the rest.

Failure 4: duplicates return the first match

After a system migration, Northgate Retail is added to the master again at row 7, still as C-1004 but now in the Enterprise segment. VLOOKUP stops at the first C-1004, in row 5, returns Mid-market, and never reads row 7. Nothing flags that the master holds two answers.

In Customers!E2, filled down to E7, with the range extended to the new row:

=COUNTIF(Customers!$A$2:$A$7,A2)>1

TRUE on rows 5 and 7. A master with one row per ID removes this failure; see how to build a customer master from scratch, and keep old and new IDs in an identifier map rather than as extra rows.

Failure 5: it cannot look left

To find the ID for "Kestrel Supplies", the name column is the one to search, but VLOOKUP only searches the first column of table_array. =VLOOKUP("Kestrel Supplies",Customers!$A$2:$D$6,1,FALSE) looks for the name among the IDs and returns #N/A. INDEX with MATCH, or XLOOKUP, returns C-1003; both are shown side by side in XLOOKUP vs VLOOKUP vs INDEX MATCH.

The check that catches all five

Two helper columns on the ledger and three numbers on a check sheet. In Ledger!D2, the segment with a visible label for misses:

=IFNA(VLOOKUP(B2,Customers!$A$2:$D$6,3,FALSE),"Not in master")

IFNA replaces only #N/A, so any other error still shows. In Ledger!E2, how many times the ID is in the master:

=COUNTIF(Customers!$A:$A,B2)

It counts the whole ID column, so a duplicate added below the lookup range is still seen. Then:

On the clean example: Enterprise 28,360, Mid-market 8,650, Small 0 and Not in master 1,980 sum to 38,990; one ID has no match and one row is labeled; no duplicates. Each failure trips at least one test:

1. Approximate match. The segment total still ties, because the 1,980 moved to Small instead of vanishing. The second test catches it: C-1099 has a match count of 0 but no row says "Not in master".
2. Inserted column. Regions replace segments, only the 1,980 lands on a listed label, and the first test shows −37,010.
3. Dirty keys. Match counts of 0 rise, and the unmatched line grows from 1,980 to 5,130.
4. Duplicates. The third test returns 1.
5. Looking left. A VLOOKUP that searches the ID column for names finds none of them, so every row is unmatched and the unmatched line is the whole ledger.

A segment report that passes all three can be sent. This is the same control-total habit as five checks before you trust a spreadsheet total.

Where it goes wrong

• Omitting FALSE returns a plausible wrong answer instead of #N/A (failure 1).
• A hard-coded column number stops pointing at the right field when the master changes (failure 2).
• IFERROR round everything turns a missing customer, and a broken formula, into a blank that is summed as nothing. Use IFNA with a label.
• Whole-column lookups such as A:D on large workbooks recalculate slowly. Convert the master to a Table and look up the Table.

When to use XLOOKUP instead

If everyone who opens the file has Microsoft 365 or Excel 2021 or later, XLOOKUP removes failures 1, 2 and 5 by design: exact match is the default, the return column is a range rather than a number, and it can return a column to the left. It does nothing about failures 3 and 4, so the checks still apply. The full comparison, with INDEX MATCH for files that must open in older Excel, is in XLOOKUP vs VLOOKUP vs INDEX MATCH.

Matching IDs without guessing

Covirage matches ledger IDs to the master with exact rules, lists the unmatched and duplicated IDs, and reconciles the joined total to the ledger before any answer is written. Its deterministic tools do the matching and the arithmetic; the external AI model only explains the result. Upload the ledger and master as they come and see how it works for finance teams. If you are maintaining lookups across many files, read four signs a spreadsheet is no longer enough. To total by a key instead of returning one value, see SUMIFS in Excel, and to merge whole files before they reach a formula, see Power Query in Excel.