INDEX

Veldhuis Installatie BV sent you its receivables aging list (ouderdomsanalyse debiteuren) at 31 December 2025: four customers down the side, four aging buckets across. For the follow-up of confirmations you want a cell that shows the open amount once you type the row number of the customer and the number of the bucket.

Formula in H3

=INDEX(B2:E5, H1, H2)

=INDEX(B2:E5; H1; H2)

Result: €8,900.00€ 8.900,00

Watch out: If someone inserts a customer between two existing rows, for example between D1002 and D1003, Excel widens B2:E5 to B2:E6 and row number 3 now points at the new customer. Your working paper still says row 3, and INDEX returns another customer's amount without any warning.

From the general ledger you summarised five cost accounts (4000, 4500, 4600, 4700, 4800) with a column per quarter of 2025. You type a quarter number in H1 and want the ledger total for that quarter in H2, to reconcile it to the client's specification. The rent invoice for the third quarter was never booked, so that cell in account 4500 is empty.

Formula in H2

=SUM(INDEX(B2:E6, 0, H1))

=SUM(INDEX(B2:E6; 0; H1))

Result: €211,100.00€ 211.100,00

Watch out: SUM treats the empty cell and a 0 the same, but AVERAGE does not: AVERAGE(INDEX(B2:E6, 0, 3)) is 211100 / 4 = 52775, and with a 0 in D3 it would be 211100 / 5 = 42220. Decide whether a missing booking means zero before you average.

=SUM(INDEX(B2:E6, 2, 0))

=SUM(INDEX(B2:E6; 2; 0))

The group controller sent an intercompany matrix: Dutch entities down the side, four counterparties across the top, with the balance receivable in each cell. You type an entity code and a counterparty code and want the balance to agree with the confirmation of the counterparty. Two codes (NL-4510 and NL-4501) differ only in the order of their digits, and the lookup must not mix them up.

Formula in H3

=INDEX(B2:E6, XMATCH(H1, A2:A6), XMATCH(H2, B1:E1))

=INDEX(B2:E6; XMATCH(H1; A2:A6); XMATCH(H2; B1:E1))

Result: €95,500.00€ 95.500,00

Watch out: Search A1:A6 instead of A2:A6 and every position is one higher, because the header now counts as position 1. NL-4501 becomes 5, and INDEX quietly returns D6, the 60000 of NL-4551, which you would then compare with the wrong confirmation.

=INDEX(B2:E6, MATCH(H1, A2:A6, 0), MATCH(H2, B1:E1, 0))

=INDEX(B2:E6; MATCH(H1; A2:A6; 0); MATCH(H2; B1:E1; 0))

=XLOOKUP(H1, A2:A6, XLOOKUP(H2, B1:E1, B2:E6))

=XLOOKUP(H1; A2:A6; XLOOKUP(H2; B1:E1; B2:E6))

You reconcile revenue (account 8000) per period with the client's revenue specification. The ledger has postings up to period 6; the specification already runs through period 8.

The number of periods you have tested is typed in F1, and the difference must compare the same periods on both sides. The formula must not be volatile, because the XAF workbook is already slow.

Formula in F2

=LET(
  periods, F1,
  ledger, SUM(B2:INDEX(B2:B9, periods)),
  spec, SUM(C2:INDEX(C2:C9, periods)),
  ledger - spec
)

=LET(
  periods; F1;
  ledger; SUM(B2:INDEX(B2:B9; periods));
  spec; SUM(C2:INDEX(C2:C9; periods));
  ledger - spec
)

Result: €11,500.00€ 11.500,00

Watch out: The mirror-image mistake is starting both ranges at the header: B1:INDEX(B1:B9, periods). The sixth cell is then B6, the header text is skipped by SUM, and you compare five periods with five periods, 2176500 - 2172000 = 4500. It looks right and reports the wrong period: the 7000 of period 6 is not in it.

=SUM(TAKE(B2:B9, F1)) - SUM(TAKE(C2:C9, F1))

=SUM(TAKE(B2:B9; F1)) - SUM(TAKE(C2:C9; F1))

=SUM(INDEX((B2:B9, C2:C9), 0, 1, 2))

=SUM(INDEX((B2:B9; C2:C9); 0; 1; 2))