Prefer XLOOKUP where you can. VLOOKUP still works in every version of Excel. If everyone who uses the file has Excel 2021 or Microsoft 365, use XLOOKUP instead.

Go to XLOOKUP

All versionsExamples need Excel 2021 or laterLookup & reference

VLOOKUP

VERT.ZOEKEN in Dutch Excel

Finds a value in the first column of a table and returns the cell a given number of columns to its right in the same row.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDEF
1AccountDescriptionBalance 31 Dec 2025AccountBalance
211001100Bank€412,380.55€ 412.380,5545004500€184,250.00€ 184.250,00=VLOOKUP(E2, A2:C8, 3, FALSE)=VLOOKUP(E2; A2:C8; 3; FALSE)
313001300Trade receivables€268,945.10€ 268.945,10
416001600Trade payables-€193,420.75€ -193.420,75
518001800VAT payable-€41,872.00€ -41.872,00
640004000Personnel costs€1,284,630.00€ 1.284.630,00
745004500Housing costs€184,250.00€ 184.250,00
880008000Revenue-€3,102,875.40€ -3.102.875,40
Note: Credit balances are negative in this list.

Result: €184,250.00€ 184.250,00

=VLOOKUP(E2, A2:C8, 3, FALSE)
=VLOOKUP(E2; A2:C8; 3; FALSE)

What VLOOKUP does and when to use it

What it does: it finds a key in the first column of a table and returns a value from the same row, counted a number of columns to the right, for example the year-end balance of a ledger account.

When to use it: in files that must also open in Excel 2019 or older, and to read the many workbooks that already use it. With TRUE it also sorts values into bands such as tax brackets, provided the first column is sorted. XLOOKUP does the same job without its restrictions.

How Excel evaluates it

Excel takes lookup_value and searches the first column of table_array. The last argument decides how:

  • FALSE: it walks down from the top and stops at the first cell that equals the key
  • TRUE: it assumes the column is sorted and settles on the largest key that does not exceed lookup_value

It then counts col_index_num columns from the left edge of table_array, in that same row, and returns the cell it lands on. If no row qualifies, the result is #N/A.

Watch out: If the fourth argument is left out, VLOOKUP does an approximate match: a missing code returns the value of a neighbouring row instead of #N/A, and nobody notices. Add FALSE (or 0) as the last argument for every lookup on codes, IDs or names.

Mental model

A ruler laid across a printed table: you slide it down the first column until it lines up with the key, then read along the ruler to the column number you were told.

Terms used on this page: workbook, array, argument, cell

Syntax

Official VLOOKUP documentation
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
ArgumentTypeMeaningDefault
lookup_valueRequiredThe value to find in the first column of the table.
table_arrayRequiredThe table; the lookup column must be its first column.
col_index_numRequiredWhich column of the table holds the answer, counting from 1.
range_lookupOptionalFALSE for an exact match; TRUE or omitted for approximate.
  • TRUEApproximate: the largest value not above the lookup value. The key column must run low to high.
  • FALSEExact: only an identical key counts. The order of the table does not matter.
TRUE

Return value:The value in column col_index_num of the matching row, or #N/A

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Year-end balance of a ledger account from the trial balance

The client sent a trial balance (saldibalans) at 31 December 2025 as a list of accounts. Your working paper needs the balance of account 4500, Housing costs, so you can compare it with the lease contracts. You want a cell where you type an account number and the balance appears.

ABCDEFG
1AccountDescriptionBalance 31 Dec 2025AccountBalance
211001100Bank€412,380.55€ 412.380,5545004500€184,250.00€ 184.250,00=VLOOKUP(E2, A2:C8, 3, FALSE)=VLOOKUP(E2; A2:C8; 3; FALSE)
313001300Trade receivables€268,945.10€ 268.945,10
416001600Trade payables-€193,420.75€ -193.420,75
518001800VAT payable-€41,872.00€ -41.872,00
640004000Personnel costs€1,284,630.00€ 1.284.630,00
745004500Housing costs€184,250.00€ 184.250,00
880008000Revenue-€3,102,875.40€ -3.102.875,40
9
Formula cell (F2)InputNote: Credit balances are negative in this list.

Formula in F2

=VLOOKUP(E2, A2:C8, 3, FALSE)
=VLOOKUP(E2; A2:C8; 3; FALSE)

Result: €184,250.00€ 184.250,00

Step by step
  1. Excel reads E2 and finds the number 4500. That is lookup_value.
  2. FALSE asks for an exact match, so Excel walks down the first column of A2:C8: 1100, no; 1300, no; 1600, no; 1800, no; 4000, no; 4500, yes. The match is the sixth row of the table, row 7 of the sheet.
  3. col_index_num is 3, so Excel counts three columns from the left edge of the table in that row: A7 is column 1, B7 column 2, C7 column 3.
  4. It returns the value of C7: 184,250184.250.
  5. Type 8000 in E2 and the result becomes -3,102,875.403.102.875,40, the credit balance of Revenue, without touching the formula.

Watch out: Leave out FALSE and type 4600 in E2: the sorted accounts let the approximate search settle on 4500 and the result is 184,250184.250, for an account that is not in the trial balance. With FALSE you get #N/A, which is the honest answer.

More on this example: why it works, 1 more pitfall

Why it works

The account number is the first column of the selected block, which is the only place VLOOKUP searches. The column number is counted inside table_array, not from column A of the sheet, so if the trial balance started in column D the 3 would still mean the third column of the block.

More to watch out for

  • If the export stores account numbers as text, the number 4500 in E2 never matches and you get #N/A for every account. Convert the column to numbers first.

Intermediate

Provision rate from a receivables aging band

In the receivables aging list (ouderdomsanalyse debiteuren) the client applies a provision rate per band of days overdue: from 0 days one rate, from 30 days the next, and so on. An invoice is almost never overdue by exactly the first day of a band, so an exact match would fail on nearly every row. You recalculate the rate next to each open invoice and copy the formula down the list.

ABCDEFG
1Days overdue fromProvision rateInvoiceDays overdueRate
2000.5%0,5%25-1017747410.0%10,0%=VLOOKUP(E2, $A$2:$B$6, 2, TRUE)=VLOOKUP(E2; $A$2:$B$6; 2; TRUE)
330302%2%25-10213030
4606010%10%25-10331212
5909025%25%
612012050%50%
7
Formula cell (F2)InputNote: The formula is in F2 and copied down to F4.

Formula in F2

=VLOOKUP(E2, $A$2:$B$6, 2, TRUE)
=VLOOKUP(E2; $A$2:$B$6; 2; TRUE)

Result: 10.0%10,0%

Step by step
  1. Excel reads E2: invoice 25-1017 is 74 days overdue.
  2. range_lookup is TRUE, so Excel treats A2:A6 as sorted and looks for the largest band start that is not above 74. 0, 30 and 60 qualify; 90 and 120 are too high. The winner is 60, in the third row of the table, A4.
  3. col_index_num is 2, so Excel returns B4: 0.10, which is 10%.
  4. With FALSE instead of TRUE the same formula returns #N/A, because no cell in A2:A6 holds exactly 74. The fourth argument is what turns a key lookup into a band lookup.
  5. Copied to F3, the formula reads E3 = 30. 30 equals a band start, so the band from 30 days applies: 0.02, which is 2%. A band start is included in its own band, not in the one below.
  6. In F4, E4 = 12 is below 30, so the band from 0 days applies: 0.005, which is 0.5%. The $ signs kept the table at A2:B6 in every copy.

Watch out: Sort the band table descending and the results become wrong or #N/A, with no warning. Keep band tables sorted ascending, or use XLOOKUP with match_mode -1, which does not depend on the order.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Approximate match answers the question 'which band does this fall into?' because it picks the last band start that the value has passed. That only works when the band starts run from small to large; the sort order is part of the formula's logic, not a nicety.

More to watch out for

  • An invoice that is not yet due has a negative number of days overdue and returns #N/A, because it is below the first band start. The table starts at 0 for that reason; test or filter those invoices first.

Variations

=XLOOKUP(E2, $A$2:$A$6, $B$2:$B$6, , -1)
=XLOOKUP(E2; $A$2:$A$6; $B$2:$B$6; ; -1)

The same band lookup with XLOOKUP: exact match or the next smaller value, and the band table may be in any order.

AdvancedExcel 2013 or later

Any fixed asset field, chosen by header name

The client's fixed asset register (activastaat) has an asset number, description, purchase date, cost and useful life. In a lookup panel on your working paper you pick an asset number and a field name to vouch to the invoice. The column number must follow the header, so the formula keeps working when the client adds a column to the register, and an unknown asset number should show a clear message.

ABCDEFGHI
1Asset no.DescriptionPurchase dateCostUseful life (years)Asset no.FA-0207
2FA-0101Delivery van15 Mar 2022€38,500.00€ 38.500,0055FieldCost
3FA-0114Office furniture9 Jan 2023€12,400.00€ 12.400,001010Value€86,250.00€ 86.250,00=IFNA(VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE), "Asset not in register")=IFNA(VLOOKUP(H1; $A$2:$E$6; MATCH(H2; $A$1:$E$1; 0); FALSE); "Asset not in register")
4FA-0207CNC milling machine3 Jun 2024€86,250.00€ 86.250,0088
5FA-0231Laptops (12)17 Feb 2025€14,760.00€ 14.760,0033
6FA-0248Forklift22 Sep 2025€27,900.00€ 27.900,0077
7
Formula cell (H3)Input

Formula in H3

=IFNA(VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE), "Asset not in register")
=IFNA(VLOOKUP(H1; $A$2:$E$6; MATCH(H2; $A$1:$E$1; 0); FALSE); "Asset not in register")

Result: €86,250.00€ 86.250,00

Step by step
  1. Excel starts with the innermost function. MATCH looks for H2, Cost, in the header row A1:E1 with match_type 0 and finds it in the fourth cell, D1. MATCH returns 4.
  2. That 4 becomes col_index_num. VLOOKUP now searches A2:A6 for H1, FA-0207, with FALSE: FA-0101, no; FA-0114, no; FA-0207, yes. The match is the third row of the table, row 4 of the sheet.
  3. VLOOKUP returns the fourth column of that row, D4: 86,25086.250.
  4. The result is not #N/A, so IFNA passes 86,25086.250 through unchanged.
  5. Change H2 to Useful life (years): MATCH returns 5 and the result is 8. Change H1 to FA-0300: VLOOKUP finds no such asset, returns #N/A, and IFNA replaces it with Asset not in register.
  6. Insert a Location column between Purchase date and Cost. The header range grows to A1:F1, MATCH now finds Cost in position 5, and the formula still returns 86,25086.250. A hard-coded 4 would now return the purchase date.

Watch out: Leave out the 0 in MATCH and it does an approximate match on the headers, which are not sorted. It can return a plausible but wrong column number, and VLOOKUP then returns the wrong field without any error.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

MATCH turns a header name into the column number VLOOKUP needs, so the weakest part of VLOOKUP, the hard-coded column, follows the data instead of the formula. Both ranges start in column A, which is why the MATCH position and the VLOOKUP column count line up. IFNA only reacts to #N/A, so a broken reference still shows as #REF! instead of being disguised as an unknown asset.

More to watch out for

  • If the header range starts in column B while the table starts in column A, every result is one column off. Both ranges must start in the same column.
  • VLOOKUP returns the first match only. If an asset number appears twice in the register, the second row is never seen, which is itself a finding.

Variations

=XLOOKUP(H1, $A$2:$A$6, XLOOKUP(H2, $A$1:$E$1, $A$2:$E$6), "Asset not in register")
=XLOOKUP(H1; $A$2:$A$6; XLOOKUP(H2; $A$1:$E$1; $A$2:$E$6); "Asset not in register")

The XLOOKUP version: the inner XLOOKUP picks the column, the outer one the row, and if_not_found replaces IFNA.

=INDEX($A$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $A$1:$E$1, 0))
=INDEX($A$2:$E$6; MATCH(H1; $A$2:$A$6; 0); MATCH(H2; $A$1:$E$1; 0))

INDEX with two MATCH functions works in every version and can also return a column to the left of the key.

MasterExcel 2021 or later

Fast exact invoice match in a sorted ledger export with two VLOOKUPs

The general ledger export (XAF auditfile) holds 200,000200.000 lines sorted ascending by invoice number, and you tick off thousands of invoices from the client's receivables list against it. Exact-match VLOOKUP scans row by row, while approximate match uses a binary search that needs only a handful of comparisons. The trick is to use the fast approximate search and then prove the invoice number really exists, so a missing invoice never borrows a neighbour's amount.

ABCDEFG
1Invoice no.RelationAmountInvoice no.250461250461
2250412250412D1001€12,450.00€ 12.450,00Amount€9,075.25€ 9.075,25=LET( key, F1, hit, IFNA(VLOOKUP(key, A2:C8, 1, TRUE), ""), IF(hit = key, VLOOKUP(key, A2:C8, 3, TRUE), "Not in ledger") )=LET( key; F1; hit; IFNA(VLOOKUP(key; A2:C8; 1; TRUE); ""); IF(hit = key; VLOOKUP(key; A2:C8; 3; TRUE); "Not in ledger") )
3250418250418D1007€3,872.50€ 3.872,50
4250433250433D1003€21,780.00€ 21.780,00
5250461250461D1001€9,075.25€ 9.075,25
6250479250479D1012€1,540.00€ 1.540,00
7250502250502D1007€33,264.00€ 33.264,00
8250517250517D1003€5,929.00€ 5.929,00
9
Formula cell (F2)InputNote: In the real export this list runs to 200,000 rows, sorted ascending by invoice number.

Formula in F2

=LET(
  key, F1,
  hit, IFNA(VLOOKUP(key, A2:C8, 1, TRUE), ""),
  IF(hit = key, VLOOKUP(key, A2:C8, 3, TRUE), "Not in ledger")
)
=LET(
  key; F1;
  hit; IFNA(VLOOKUP(key; A2:C8; 1; TRUE); "");
  IF(hit = key; VLOOKUP(key; A2:C8; 3; TRUE); "Not in ledger")
)

Result: €9,075.25€ 9.075,25

Step by step
  1. LET names the input: key becomes 250461.
  2. hit is the first VLOOKUP, with col_index_num 1 and TRUE. It does a binary search on A2:A8 and returns the largest invoice number that is not above 250461. That is 250461 itself, in A5, so hit is 250461. IFNA stays idle because there is no error.
  3. IF compares hit with key: 250461 = 250461 is TRUE, so the invoice exists in the ledger.
  4. The second VLOOKUP runs the same fast search and returns column 3 of that row, C5: 9,075.259.075,25.
  5. Type 250470 in F1. The approximate search settles on 250461, so hit is 250461. 250461 = 250470 is FALSE, and the result is Not in ledger instead of the 9,075.259.075,25 that a lone approximate VLOOKUP would have returned.
  6. Type 250100, smaller than every invoice number. The first VLOOKUP returns #N/A, IFNA turns it into empty text, empty text does not equal 250100, and the result is again Not in ledger instead of an error.

Watch out: The mirror-image mistake: dropping the IF check and keeping only the second VLOOKUP with TRUE. It looks identical for every invoice that exists, and quietly returns a neighbour's amount for every invoice that does not, so a missing invoice is ticked off as agreed.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Approximate match on sorted data halves the search space with every comparison, so 200,000200.000 rows take about 18 steps instead of up to 200,000200.000. Returning column 1 first gives you the invoice number that was actually found, and comparing it with the one you asked for restores exact-match safety.

Do not use this construction on data that is not reliably sorted, and do not bother with it on small lists or in current Microsoft 365, where exact lookups are already much faster than they used to be: measure before you optimize.

The alternative is XLOOKUP with search_mode 2, which does the binary search and the existence check in one function, at the cost of requiring Excel 2021 or later.

More to watch out for

  • One unsorted row, for example a late posting appended at the bottom of the export, can send the binary search to the wrong place. The IF check then reports Not in ledger for an invoice that is there. Re-sort after every import.
  • Invoice numbers stored as text in the export and as numbers in F1 never compare as equal. Check the column with ISNUMBER.

Variations

=XLOOKUP(F1, A2:A8, C2:C8, "Not in ledger", 0, 2)
=XLOOKUP(F1; A2:A8; C2:C8; "Not in ledger"; 0; 2)

XLOOKUP with an exact match and search_mode 2 (binary search on ascending data): the same speed and the same safety in one function.

Mode explorer

Type a lookup value, pick the column to return and the range_lookup setting, and see which row VLOOKUP takes it from.

ABCD
1Days overdue fromProvision rateRate
2000.5%0,5%0.10,1=VLOOKUP("x", $A$2:$B$8, 2, TRUE)=VLOOKUP("x"; $A$2:$B$8; 2; TRUE)
330302%2%
4606010%10%
5909025%25%
612012050%50%
718018075%75%
8365365100%100%
  • Formula cell
  • Where the search lands
  • What it returns
=VLOOKUP(74, $A$2:$B$8, 2, TRUE)
=VLOOKUP(74; $A$2:$B$8; 2; TRUE)

Result: 0.10,1

74 is not in the table. TRUE takes the row of 60, the largest value not above 74: rate 10%.

=VLOOKUP(74, $A$2:$B$8, 2, FALSE)
=VLOOKUP(74; $A$2:$B$8; 2; FALSE)

Result: #N/A

74 is not in the table, so an exact search finds nothing and gives #N/A.

=VLOOKUP(74, $A$2:$B$8, 1, TRUE)
=VLOOKUP(74; $A$2:$B$8; 1; TRUE)

Result: 6060

=VLOOKUP(74, $A$2:$B$8, 1, FALSE)
=VLOOKUP(74; $A$2:$B$8; 1; FALSE)

Result: #N/A

Common mistakes

SymptomCauseFix
The wrong field after inserting a columnA column is inserted inside the table and the formula now returns the wrong field, because col_index_num is still 3.Let MATCH find the column number from the header, as in the Advanced example, or switch to XLOOKUP, which points at the answer column directly.
The answer column sits left of the keyThe answer column is to the left of the key column, and no col_index_num reaches it.Use INDEX with MATCH, or XLOOKUP. Rearranging the source table only to please VLOOKUP tends to break other formulas.
#N/A in the lower rows after copying downThe formula is copied down without $, so table_array shifts one row per copy and the lower rows miss keys at the top of the table.Write the table as $A$2:$C$50, or turn it into a Table and use its name.