Combinatie

De klant vinden via een bankomschrijving

Knip het factuurnummer uit een bankomschrijving met TEKST.NA en TEKST.VOOR en zoek de klant daarna op in de debiteurenlijst met X.ZOEKEN.

Gebruikte functies:TEKST.NATEKST.VOORX.ZOEKENExcel 2024 of nieuwer

Moeilijkheidsgraad

Uitgewerkt voorbeeld

De klant achter een bankontvangst

Situatie

Bij de debiteurenwerkzaamheden bij Veldhuis Installatie BV koppel je bankontvangsten aan de debiteurenlijst. In elke omschrijving staat het factuurnummer achter het label REF:. Je wilt de naam van de klant naast de ontvangst zien, zonder het nummer over te typen, en een duidelijke melding als de factuur niet in de lijst staat.

De gegevens

ABCDEFGHI
1Factuurnr.KlantOpenstaand bedragBankomschrijvingSEPA OVERBOEKING REF:25-0433 BETALING DEBITEUR
225-0412Mulder Bouw BV€12,100.00€ 12.100,00
325-0415De Vries Transport€8,470.00€ 8.470,00KlantBakker Groothandel=X.ZOEKEN(TEKST.VOOR(TEKST.NA(H1, "REF:"), " ", 1, 0, 1), A2:A7, B2:B7, "Niet in debiteuren", 0)=X.ZOEKEN(TEKST.VOOR(TEKST.NA(H1; "REF:"); " "; 1; 0; 1); A2:A7; B2:B7; "Niet in debiteuren"; 0)
425-0420Jansen Installatie€3,025.00€ 3.025,00
525-0433Bakker Groothandel€15,730.00€ 15.730,00
625-0441Smit Verhuur€6,050.00€ 6.050,00
725-0447Van Dijk Schilderwerk€1,210.00€ 1.210,00
8
Formulecel (H3)Invoer

Formule in H3

=X.ZOEKEN(TEKST.VOOR(TEKST.NA(H1, "REF:"), " ", 1, 0, 1), A2:A7, B2:B7, "Niet in debiteuren", 0)
=X.ZOEKEN(TEKST.VOOR(TEKST.NA(H1; "REF:"); " "; 1; 0; 1); A2:A7; B2:B7; "Niet in debiteuren"; 0)

Resultaat: Bakker Groothandel

Hoe de onderdelen samenwerken

De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.

  1. =TEKST.NA(H1, "REF:")
    =TEKST.NA(H1; "REF:")

    Geeft alles terug wat na het label REF: in de omschrijving staat. Het resultaat is de tekst 25-0433 BETALING DEBITEUR: achter het factuurnummer staat nog de rest van de omschrijving.

  2. =TEKST.VOOR(TEKST.NA(H1, "REF:"), " ", 1, 0, 1)
    =TEKST.VOOR(TEKST.NA(H1; "REF:"); " "; 1; 0; 1)

    TEKST.VOOR houdt het deel vóór de eerste spatie over, zodat alleen 25-0433 overblijft. Het laatste argument, 1, laat het einde van de tekst als scheidingsteken gelden, zodat ook een factuurnummer helemaal aan het eind van een omschrijving wordt teruggegeven.

  3. =TEKST.VOOR(TEKST.NA(H1, "REF:"), " ", 1, 0, 1)=A2:A7
    =TEKST.VOOR(TEKST.NA(H1; "REF:"); " "; 1; 0; 1)=A2:A7

    Vergelijkt het uitgeknipte nummer met de zes factuurnummers in A2:A7. Het resultaat is één kolom: ONWAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR. Alleen de vierde factuur, 25-0433, komt overeen.

  4. =X.ZOEKEN(TEKST.VOOR(TEKST.NA(H1, "REF:"), " ", 1, 0, 1), A2:A7, B2:B7, "Niet in debiteuren", 0)
    =X.ZOEKEN(TEKST.VOOR(TEKST.NA(H1; "REF:"); " "; 1; 0; 1); A2:A7; B2:B7; "Niet in debiteuren"; 0)

    X.ZOEKEN zoekt 25-0433 in A2:A7, vindt het in de vierde rij en geeft de klant uit dezelfde rij van B2:B7 terug: Bakker Groothandel. Staat het nummer niet in de lijst, dan toont het de tekst voor niet gevonden in plaats van #N/B.

Stap voor stap

  1. TEKST.NA(H1; "REF:") knipt de omschrijving bij het label en houdt over wat erachter staat: 25-0433 BETALING DEBITEUR.
  2. TEKST.VOOR(...; " "; 1; 0; 1) houdt het deel vóór de eerste spatie over: 25-0433.
  3. X.ZOEKEN vergelijkt 25-0433 met A2:A7: 25-0412, 25-0415 en 25-0420 komen niet overeen, 25-0433 is de vierde waarde, dus de overeenkomst staat op plek 4.
  4. Plek 4 van B2:B7 (Mulder Bouw BV, De Vries Transport, Jansen Installatie, Bakker Groothandel) is Bakker Groothandel, het resultaat.

Waarom het werkt

Het label REF: is een vast ankerpunt, dus TEKST.NA hoeft niet te weten waar in de omschrijving het nummer staat of hoe lang de tekst ervoor is. TEKST.VOOR knipt daarna de rest af bij de volgende spatie. Wat overblijft is een schone sleutel, en X.ZOEKEN doet daarmee een gewone exacte zoekactie.

Let op

  • Staat er geen REF: in de omschrijving, dan geeft TEKST.NA #N/B terug en geeft X.ZOEKEN die fout door. De tekst voor niet gevonden verschijnt alleen als het label er wel staat maar het nummer niet in de lijst voorkomt.
  • Standaard let het label op hoofdletters. Schrijft een bank Ref:, dan krijg je #N/B. Zet het vierde argument van TEKST.NA op 1 om hoofdletters te negeren.
  • Sommige banken zetten direct achter het nummer een teken, bijvoorbeeld 25-0433/. De knip bij de spatie laat de schuine streep dan in de sleutel staan en de zoekactie mislukt. Controleer een paar omschrijvingen van elke bank voordat je de formule doorvoert.

Varianten

=ALS.FOUT(INDEX(B2:B7, VERGELIJKEN(DEEL(H1, VIND.ALLES("REF:", H1) + 4, 7), A2:A7, 0)), "Niet in debiteuren")
=ALS.FOUT(INDEX(B2:B7; VERGELIJKEN(DEEL(H1; VIND.ALLES("REF:"; H1) + 4; 7); A2:A7; 0)); "Niet in debiteuren")

De oudere manier zonder TEKST.NA: VIND.ALLES geeft het label op plek 18, dus DEEL begint op 18 + 4 = 22 en neemt 7 tekens (25-0433). Dit werkt alleen zolang elk factuurnummer precies 7 tekens heeft, en ALS.FOUT verbergt ook een ontbrekend label.

=ALS.FOUT(INDEX(B2:B7, VERGELIJKEN(WAAR, ISGETAL(VIND.SPEC(A2:A7, H1)), 0)), "Niet in debiteuren")
=ALS.FOUT(INDEX(B2:B7; VERGELIJKEN(WAAR; ISGETAL(VIND.SPEC(A2:A7; H1)); 0)); "Niet in debiteuren")

Werkt zonder label: het controleert welk factuurnummer uit A2:A7 ergens in de omschrijving voorkomt. Handig als het nummer geen vaste plek heeft, maar het kan een nummer vinden dat in een langer nummer zit.

Alle combinaties