All versionsExamples need Excel 2021 or laterLookup & reference

MATCH

VERGELIJKEN in Dutch Excel

Tells you where a value sits in a single row or column, as a position number counted from the start of that range.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDEFG
1Entry no.JournalAccountDebitEntry no.Position in export
225-0407VRK13001300€12,100.00€ 12.100,0025-041233=MATCH(F2, A2:A7, 0)=MATCH(F2; A2:A7; 0)
325-0415BNK11001100€4,500.00€ 4.500,00
425-0412INK45004500€3,250.00€ 3.250,00
525-0431MEM40004000€18,900.00€ 18.900,00
625-0409VRK13001300€6,050.00€ 6.050,00
725-0420INK16001600€2,400.00€ 2.400,00

Result: 3

=MATCH(F2, A2:A7, 0)
=MATCH(F2; A2:A7; 0)

What MATCH does and when to use it

What it does: it returns the position of a value in a range, for example 3 when the invoice number is the third entry in the list.

When to use it: when you need a position: the row of a key for INDEX, the column of a header, or the band a value falls into. If you only want the value next to the key and have Excel 2021 or later, XLOOKUP does it in one step. XMATCH is the newer version with an exact match as its default.

How Excel evaluates it

Excel reads lookup_value and then looks at match_type:

  • 0: walks the range from the first cell and stops at the first cell that equals the value, ignoring case and honouring wildcards in text
  • 1: assumes the range is sorted ascending and returns the position of the largest value that does not exceed lookup_value
  • -1: assumes descending order and returns the position of the smallest value that is not below it

The result is the position within the range, counted from 1. If nothing qualifies, the result is #N/A.

Watch out: Without 0 as the third argument MATCH searches approximately, not exactly: on an unsorted list it returns a wrong position or #N/A even though the value is there.

Mental model

A cloakroom ticket: MATCH does not hand you the coat, it hands you the number of the hook. INDEX is the attendant who fetches what hangs there.

Terms used on this page: wildcard, range, cell

Syntax

Official MATCH documentation
MATCH(lookup_value, lookup_array, [match_type])
MATCH(lookup_value; lookup_array; [match_type])
ArgumentTypeMeaningDefault
lookup_valueRequiredThe value whose position you want.
lookup_arrayRequiredThe single row or column to search.
match_typeOptional0 exact; 1 largest value <= (ascending data); -1 smallest value >= (descending data).
  • 1Largest value that is not above the lookup value; the list must run low to high.
  • 0Only an exact match counts; the order of the list does not matter.
  • -1Smallest value that is not below the lookup value; the list must run high to low.
1

Return value:A number: the position of the match within lookup_array, 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

Position of a journal entry in the auditfile export

You have the journal lines of the XAF auditfile in a sheet. The client's controller asks you to look at entry 25-0412, and you want to know how far down the export it sits, so you can point to the right line. You need the position in the list, not the amount.

ABCDEFGH
1Entry no.JournalAccountDebitEntry no.Position in export
225-0407VRK13001300€12,100.00€ 12.100,0025-041233=MATCH(F2, A2:A7, 0)=MATCH(F2; A2:A7; 0)
325-0415BNK11001100€4,500.00€ 4.500,00
425-0412INK45004500€3,250.00€ 3.250,00
525-0431MEM40004000€18,900.00€ 18.900,00
625-0409VRK13001300€6,050.00€ 6.050,00
725-0420INK16001600€2,400.00€ 2.400,00
8
Formula cell (G2)Input

Formula in G2

=MATCH(F2, A2:A7, 0)
=MATCH(F2; A2:A7; 0)

Result: 33

Step by step
  1. Excel reads F2 and finds the text 25-0412. That is lookup_value.
  2. match_type is 0, so Excel looks for an exact match from the top of A2:A7: 25-0407, no; 25-0415, no (similar digits, different number); 25-0412, yes.
  3. The match is the third cell of the range, A4. MATCH returns 3: the position, not the debit amount and not the account.
  4. A4 is row 4 of the sheet, but the position is 3, because counting starts at A2, the first cell of the range.
  5. Type 25-0420 in F2 and the result becomes 6.

Watch out: Without the 0, MATCH assumes the entry numbers are sorted and searches by halving the list. On this unsorted export it can return a wrong position or #N/A, with no hint that something is off.

More on this example: why it works

Why it works

The export is in the order the client's system wrote it, not sorted by entry number, so only an exact search can be trusted, and match_type 0 is exactly that. Because MATCH reports a position, the answer already means something here: it is the line of the list where the entry sits.

Intermediate

Ageing band number from days overdue

For the receivables ageing analysis you sort each open invoice into a band by days overdue:

  • 0 to 30
  • 31 to 60
  • 61 to 90
  • 91 to 120
  • over 120

The band number feeds a summary elsewhere in the workbook. An invoice that is 47 days overdue must land in the band that starts at 31.

ABCDEFG
1Days overdue fromBandRelationDays overdueBand no.
2000 to 30 daysD1001474722=MATCH(E2, $A$2:$A$6, 1)=MATCH(E2; $A$2:$A$6; 1)
3313131 to 60 daysD10023030
4616161 to 90 daysD1003134134
5919191 to 120 days
6121121Over 120 days
7
Formula cell (F2)InputNote: The formula is in F2 and copied down to F4.

Formula in F2

=MATCH(E2, $A$2:$A$6, 1)
=MATCH(E2; $A$2:$A$6; 1)

Result: 22

Step by step
  1. Excel reads E2: the open invoice of D1001 is 47 days overdue.
  2. match_type 1 tells MATCH that A2:A6 is sorted ascending and that it should return the largest value not above 47. 0 and 31 qualify; 61 is too high.
  3. 31 is the second cell of the range, A3, so MATCH returns 2: band 2, 31 to 60 days.
  4. With match_type 0 the same formula returns #N/A, because 47 does not appear in A2:A6. The third argument is what turns 'find this value' into 'find the band'.
  5. Copied to F3, the formula reads 30. Only 0 is not above 30, because 31 is higher, so the result is 1: an invoice 30 days overdue is still in the first band.
  6. In F4, 134 is above the last start, 121, so the result is 5, over 120 days. The $ signs kept the range at A2:A6 in every copy.

Watch out: An invoice that is not yet due has negative days overdue, which is below 0 and returns #N/A. Decide whether those invoices belong in a 'not due' band and handle them before MATCH.

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

Why it works

In a sorted list of band starts, 'the last start you have passed' is the band you are in, and that is what match_type 1 finds. Because the band starts are listed in band order, the position is the band number itself, so no second column is needed.

More to watch out for

  • If someone adds a band at the bottom of the table out of order, every result above it can shift. Keep the band table sorted ascending.

Variations

=INDEX($B$2:$B$6, MATCH(E2, $A$2:$A$6, 1))
=INDEX($B$2:$B$6; MATCH(E2; $A$2:$A$6; 1))

Wrap it in INDEX to show the band label, 31 to 60 days, instead of the number.

Advanced

Sample size from a population band and risk level

Your audit methodology sets the number of items to test in a sample by the value of the population and by the assessed risk. Population value bands run down the side and risk levels across the top. For any population value and risk level you need the sample size, where the value falls between bands and the risk level must match exactly.

ABCDEFGH
1Population fromLowMediumHighRisk levelMedium
2€0.00€ 0,00151525254040Population value34000003400000
3€500,000.00€ 500.000,00202035355555Sample size4545=INDEX(B2:D5, MATCH(G2, A2:A5, 1), MATCH(G1, B1:D1, 0))=INDEX(B2:D5; MATCH(G2; A2:A5; 1); MATCH(G1; B1:D1; 0))
4€2,000,000.00€ 2.000.000,00252545457070
5€10,000,000.00€ 10.000.000,00303055559090
6
Formula cell (G3)Input

Formula in G3

=INDEX(B2:D5, MATCH(G2, A2:A5, 1), MATCH(G1, B1:D1, 0))
=INDEX(B2:D5; MATCH(G2; A2:A5; 1); MATCH(G1; B1:D1; 0))

Result: 4545

Step by step
  1. Excel evaluates the first MATCH: it looks for 3400000 in A2:A5 with match_type 1. 0, 500000 and 2000000 are not above 3400000; 10000000 is. The largest qualifying value, 2000000, is the third cell, so this MATCH returns 3.
  2. Excel evaluates the second MATCH: it looks for Medium in B1:D1 with match_type 0. Low, no; Medium, yes. It returns 2.
  3. INDEX receives row 3 and column 2 of B2:D5. Row 3 is the band from 2000000 (row 4 of the sheet), column 2 is column C, so INDEX returns C4.
  4. C4 holds 45, the sample size: 45 items to test for a population of 3,400,0003.400.000 at medium risk.
  5. Change G2 to 10000000 and the first MATCH returns 4 (an exact hit on a band start), giving 55. Change G1 to High and the second MATCH returns 3.

Watch out: If the population bands are not sorted ascending, match_type 1 returns a wrong row without any error, and the working paper shows a sample size from another band.

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

Why it works

The two MATCH functions use different match types because the two questions differ: the population value needs 'which band', the risk level needs 'which exact column'. INDEX only combines two position numbers, so each direction can use whatever search logic suits it. All three ranges start at the same corner, so the positions line up.

More to watch out for

  • Leave out the 0 in the risk MATCH and it searches the headers as if they were sorted. Depending on the labels, it may still find the right column today and the wrong one after a level is renamed or added.
  • A population value below 0 is under the first band and returns #N/A, for example a net credit balance.

Variations

=INDEX(B2:D5, XMATCH(G2, A2:A5, -1), XMATCH(G1, B1:D1))
=INDEX(B2:D5; XMATCH(G2; A2:A5; -1); XMATCH(G1; B1:D1))

With XMATCH, match_mode -1 means exact or next smaller and does not require sorted data; exact match is the default for the risk level.

=XLOOKUP(G2, A2:A5, XLOOKUP(G1, B1:D1, B2:D5), , -1)
=XLOOKUP(G2; A2:A5; XLOOKUP(G1; B1:D1; B2:D5); ; -1)

Nested XLOOKUP does the same in Excel 2021 and later: the inner one picks the risk column, the outer one the band.

MasterExcel 2021 or later

First journal line above performance materiality

In journal entry testing you go through the lines in entry order and want the first entry whose amount exceeds performance materiality, because that is where you start your inspection. MATCH normally searches for a value, but here the question is 'where does a condition first become true?'.

ABCDEFG
1Entry no.JournalAmountPerformance materiality4000040000
225-0951VRK€18,450.00€ 18.450,00First entry above it25-0974=LET( amount, C2:C9, limit, F1, pos, MATCH(TRUE, amount > limit, 0), IFNA(INDEX(A2:A9, pos), "No entry above the limit") )=LET( amount; C2:C9; limit; F1; pos; MATCH(TRUE; amount > limit; 0); IFNA(INDEX(A2:A9; pos); "No entry above the limit") )
325-0958INK€7,200.00€ 7.200,00
425-0962MEM€31,000.00€ 31.000,00
525-0967BNK€3,100.00€ 3.100,00
625-0971VRK€9,800.00€ 9.800,00
725-0974MEM€41,500.00€ 41.500,00
825-0980INK€2,750.00€ 2.750,00
925-0983BNK€52,000.00€ 52.000,00
10
Formula cell (F2)Input

Formula in F2

=LET(
  amount, C2:C9,
  limit, F1,
  pos, MATCH(TRUE, amount > limit, 0),
  IFNA(INDEX(A2:A9, pos), "No entry above the limit")
)
=LET(
  amount; C2:C9;
  limit; F1;
  pos; MATCH(TRUE; amount > limit; 0);
  IFNA(INDEX(A2:A9; pos); "No entry above the limit")
)

Result: 25-0974

Step by step
  1. LET names the inputs: amount is C2:C9 and limit is 40000.
  2. amount > limit compares every amount with 40000 and produces an array of eight logical values: 18450, 7200, 31000, 3100 and 9800 give FALSE; 41500 gives TRUE; 2750 gives FALSE; 52000 gives TRUE. The array is {FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE}.
  3. MATCH looks for TRUE in that array with match_type 0. The first TRUE is in position 6, so pos is 6.
  4. INDEX returns the sixth cell of A2:A9, which is A7: entry 25-0974. IFNA lets the entry number through because it is not an error.
  5. Raise F1 to 45000 and pos becomes 8, entry 25-0983, because 41500 is no longer above the limit.
  6. Raise F1 to 60000. No amount is above 60000, the array holds only FALSE, MATCH returns #N/A, INDEX passes the #N/A on, and IFNA shows No entry above the limit.

Watch out: The mirror-image mistake: leaving out the 0. MATCH then treats the array as sorted and searches by halving it. On a mix of FALSE and TRUE the position it returns is not reliably the first TRUE, so you may inspect the wrong entry without any error.

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

Why it works

MATCH does not care where its lookup_array comes from: a comparison that produces an array of TRUE and FALSE is a valid list to search, and the first TRUE marks the first line that meets the condition. LET keeps the condition readable and lets you change the rule in one place.

Do not use this construction when you have Excel 2021 or later and only need the entry number: XLOOKUP(TRUE, C2:C9 > F1, A2:A9, ...) does it in one function, at the cost of not working in older versions.

In Excel 2019 and older, this MATCH needs to be confirmed with Ctrl+Shift+Enter and LET is not available.

More to watch out for

  • If the export holds an amount as text, for example 41.500 stored as a text string, Excel treats text as larger than any number, so that line is reported as the first one above the limit. Convert the column to numbers first.
  • amount and the INDEX range must cover the same rows. C2:C9 against A3:A10 shifts every answer one entry.

Variations

=XLOOKUP(TRUE, C2:C9 > F1, A2:A9, "No entry above the limit")
=XLOOKUP(TRUE; C2:C9 > F1; A2:A9; "No entry above the limit")

Excel 2021 and later: XLOOKUP searches the TRUE/FALSE array directly and has its own if_not_found.

=MATCH(TRUE, C2:C9 > F1, 0)
=MATCH(TRUE; C2:C9 > F1; 0)

Only the position of the line within the export (6), for example to jump to it with a hyperlink.

Mode explorer

Type a value and pick a match type, and see which position MATCH returns on a list sorted low to high.

ABCD
1Days overdue fromBandPosition
2000 to 30 days22=MATCH("x", $A$2:$A$7, 1)=MATCH("x"; $A$2:$A$7; 1)
3313131 to 60 days
4616161 to 90 days
5919191 to 120 days
6121121121 to 180 days
7181181Over 180 days
  • Formula cell
  • Where the search lands
=MATCH(47, $A$2:$A$7, 1)
=MATCH(47; $A$2:$A$7; 1)

Result: 22

47 is not in the list. Type 1 takes the largest value not above it, 31, at position 2.

=MATCH(47, $A$2:$A$7, 0)
=MATCH(47; $A$2:$A$7; 0)

Result: #N/A

47 is not in the list, so an exact match finds nothing.

=MATCH(47, $A$2:$A$7, -1)
=MATCH(47; $A$2:$A$7; -1)

Result: 66

Type -1 expects a list that runs high to low. This list runs low to high, so position 6 means nothing.

Common mistakes

SymptomCauseFix
Wrong row found in an unsorted listmatch_type is left out of a lookup on an unsorted list.Add 0 as the third argument for every exact lookup.
Position is off from the sheet rowThe position is used as a row number on the sheet. MATCH returns 4 for a value in A5 when the range starts at A2.Feed the position to INDEX with a range that starts in the same row as lookup_array.
#N/A with a wildcardA wildcard such as "Ber*" returns #N/A, because match_type is 1 or the range holds numbers.Wildcards work only with match_type 0 and only on text cells.
#N/A or nonsense with match_type -1match_type -1 is used on data sorted ascending, and the result is #N/A or nonsense.-1 needs descending data. For ascending bands use 1.

Used in combinations

  • Find the customer from a bank descriptionCut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.Excel 2024 or later
  • Invoices missing from the ledger with FILTER, ISNA and XMATCHList the invoices on the receivables specification that are not in the general ledger: XMATCH looks each one up, ISNA turns every miss into TRUE, and FILTER keeps those rows, with a message when nothing is missing.Excel 2021 or later
  • Entries posted on weekends and public holidays with FILTER, WEEKDAY and COUNTIFList the journal entries dated on a Saturday, a Sunday or a Dutch public holiday: WEEKDAY flags the weekends, COUNTIF checks the date against a holiday list, and FILTER keeps the flagged rows.Excel 2021 or later