IFNA

You recalculate the VAT on the sales journal (VRK) of Veldhuis Installatie BV. The client's VAT code table gives the rate per code.

A journal line with a code that is not in the table must get a rate of 0 instead of an error, so the recalculated VAT column still adds up and the difference with the booked VAT shows you the line to investigate.

Formula in E2

=IFNA(VLOOKUP(D2, A2:B4, 2, FALSE), 0)

=IFNA(VLOOKUP(D2; A2:B4; 2; FALSE); 0)

Result: 0%0%

Watch out: A rate of 0 is also the real rate of code V0. An unknown code and a zero-rated code now look the same in the rate column, so check the difference with the booked VAT, or test the code with COUNTIF, before you accept a line.

You compare the balance per invoice in the general ledger with the receivables aging list that Veldhuis Installatie BV supplied. An invoice that is not on the aging list must show 'Not in list'.

One line on the aging list has the text 'to be agreed' instead of an amount, and that problem must stay visible, because it needs a different action: asking the client.

Formula in G2

=IFNA(F2 - VLOOKUP(E2, $A$2:$C$6, 3, FALSE), "Not in list")

=IFNA(F2 - VLOOKUP(E2; $A$2:$C$6; 3; FALSE); "Not in list")

Result: Not in list

Watch out: Without the $ signs, the copy in G4 would search A4:C8, which starts below 25-3108. G4 would then say Not in list for an invoice that is on the list.

=F2 - IFNA(VLOOKUP(E2, $A$2:$C$6, 3, FALSE), "Not in list")

=F2 - IFNA(VLOOKUP(E2; $A$2:$C$6; 3; FALSE); "Not in list")

Veldhuis Installatie BV changed its chart of accounts during the year, so the XAF auditfile contains both new and old account numbers. To map an account to its financial statement line, the lookup must first try the new account column and, only if that fails, the old account column, and show 'Unknown account' when neither matches.

Formula in F2

=IFNA(INDEX(C2:C7, MATCH(F1, A2:A7, 0)), IFNA(INDEX(C2:C7, MATCH(F1, B2:B7, 0)), "Unknown account"))

=IFNA(INDEX(C2:C7; MATCH(F1; A2:A7; 0)); IFNA(INDEX(C2:C7; MATCH(F1; B2:B7; 0)); "Unknown account"))

Result: Housing costs

Watch out: If a new account number happens to equal an old number of a different account, the first lookup succeeds and returns the wrong statement line. Nothing signals the mix-up; check that the two columns cannot overlap.

=XLOOKUP(F1, A2:A7, C2:C7, XLOOKUP(F1, B2:B7, C2:C7, "Unknown account"))

=XLOOKUP(F1; A2:A7; C2:C7; XLOOKUP(F1; B2:B7; C2:C7; "Unknown account"))

You compare four ledger balances from the XAF auditfile with the trial balance (saldibalans) of Veldhuis Installatie BV and want the sum of the absolute differences in one cell.

Account 1700 is not on the client's trial balance; for this estimate its specification counts as zero, so the whole ledger balance is a difference, instead of the total becoming unusable. Any other problem in the trial balance must still produce an error.

Formula in H2

=LET(
  accounts, E2:E5,
  ledger, F2:F5,
  spec, IFNA(XLOOKUP(accounts, A2:A6, C2:C6), 0),
  SUM(ABS(ledger - spec))
)

=LET(
  accounts; E2:E5;
  ledger; F2:F5;
  spec; IFNA(XLOOKUP(accounts; A2:A6; C2:C6); 0);
  SUM(ABS(ledger - spec))
)

Result: €7,450.00€ 7.450,00

Watch out: The mirror-image mistake: IFERROR instead of IFNA. On this data the result is the same 7450, so it looks right, but an error value in the balance column (such as #VALUE! or #REF!) would then also count as zero and the difference would come out wrong without any warning.

=SUMPRODUCT(ABS(F2:F5 - IFNA(XLOOKUP(E2:E5, A2:A6, C2:C6), 0)))

=SUMPRODUCT(ABS(F2:F5 - IFNA(XLOOKUP(E2:E5; A2:A6; C2:C6); 0)))