waarde geven op basis van zoekfunctie bij lege cellen

[marco1612]

Hallo,

Ik ben alweer een paar dagen bezig een bepaalde zoekfunctie in elkaar te zetten in excel, onder meer met behulp van andere antwoorden op dit forum, maar ik kom er toch niet helemaal uit.

Ik heb een werkblad met basisgegevens. Deze bestaat uit 4 kolommen. De eerste 3 kolommen betreft het adres, opgedeeld in straat, nummer en toevoeging. De 4e kolom bevat een onderwerp. Het tweede werkblad bevat (in 1 kolom) de adressen + adressen die niet in de basisgegevens staan. In de 2e kolom staat een rekenveld die het onderwerp plaatst achter het juiste adres of #N/B als een adres niet bestaat in de basisgegevens.

Op zich werkt het ... behalve als ik in de basisgegevens een deel van de straat/nummer of toevoeging verwijder, dus een lege cel heb; dan krijg ik allemaal #ÑB, ook bij de rijen gegevens zonder lege cellen die wel een resultaat zouden moeten geven. :evil: Ik heb een voorbeeld bijgevoegd.

Hoe krijg ik het rekenveld zo dat het onderwerp wordt geplaatst achter het adres in het 2e werkblad, ook als er lege cellen tussen zitten?

Bekijk bijlage Zoek specifieke tekst.xlsx

Alvast dank!

Marco

 

[MarcelBeug]

Als ik het verhaal goed begrijp, dan zit het probleem in de gedefinieerde namen.

Bijvoorbeeld KolomC =VERSCHUIVING(basis!$C$1;1;0;AANTALARG(basis!$C:$C)-1;1)
Als je data loopt van regel 2 t/m 4, zoals in het voorbeeld, dan levert dit op: $C$2:$C$4, waarbij de 4 dus gelijk is aan het aantal argumenten in kolom C.
Als je een cel leeg maakt (bijvoorbeeld C3), dan wordt KolomC: $C$2:$C$3

De remedie is om in het stukje met AANTALARG een kolom op te nemen waarin geen lege cellen voorkomen; ik denk kolom A (of anders D).

Dus de formule voor gedefinieerde naam KolomC wordt dan: =VERSCHUIVING(basis!$C$1;1;0;AANTALARG(basis!$A:$A)-1;1)

Overeenkomstig voor de overige gedefinieerde namen.

 

[marco1612]

Dank! Dit lijkt inderdaad een stuk de goede richting in te gaan. Enkel lijkt de berekening zich niets aan te trekken van de gegevens kolom C in de basisgegevens. Als ik die verander of weghaal, hij blijft dan hetzelfde resultaat geven, terwijl het de bedoeling is dat hij enkel een resultaat moet geven als de berekening zowel voldoet aan kolom a, b en c. Vreemd!

Ik heb het bestandje weer bijgevoegd. Hopelijk lukt het om me nog een zetje verder te helpen.Bekijk bijlage Zoek specifieke tekst.xlsx

 

[HSV]

Je moet eigenlijk andersom denken met die formule.
Je hebt kolom B en C niet nodig.

In het voorbeeldbestand (zoeken in bankafschrift) is het denk ik iets duidelijker.
In je eigen bestand heb ik geen gebruik gemaakt van je gedefinieerde namen.

 

[marco1612]

Dag Harry,

Dank voor dat prachtige voorbeeldje; dat ga ik zeker gebruiken in mijn eigen boekhouding!

In dit geval werkt het echter niet. Er moet zowel voldaan zijn aan kolom a, b als c, voordat hij het resultaat weergeeft. In jouw voorbeeld is het al voldoende als voldaan is aan kolom a (toch?).

De basisgegevens (het adres verspreid in 3 kolommen) krijg ik zo aangeleverd. Ik kan natuurlijk een extra kolom aan de basisgegevens toevoegen, waarbij ik die 3 kolommen samenvoeg, maar ik denk dat het toch via een formule ineens zou moeten kunnen. Het lijkt ook wel dat ik er heel dichtbij ben, maar dat het net niet lukt. Heel frustrerend ;-) Misschien iemand nog een idee?

 

[MarcelBeug]

Het lijkt erop dat je een exacte match zoekt.
Zonder hulpkolom gaat het lastig worden.
Stel dat je in 'berekening' "Blaak" hebt in kolom A en in 'basis' een regel hebt met alleen "Blaak", dan levert KolomA&" "&KolomB&"-"&KolomC voor die regel op "Blaak -" en die vind je niet terug in kolom A van 'berekening'.

Ik heb een HulpKolom - met overeenkomstige gedefinieerde naam - aan 'Basis' toegevoegd met =A2&ALS(B2="";"";" "&B2)&ALS(C2="";"";"-"&C2)

De formule in 'berekening'!B2 wordt dan =INDEX(kolomD;VERGELIJKEN(A2;HulpKolom;0))

 

[marco1612]

Ik heb het inderdaad zo overgenomen en het werkt! Dank voor de hulp.

Het topic kan gesloten worden!

 

[MarcelBeug]

Graag gedaan.

 

[snb]

Zonder hulpkolom met de matrixformule:

=INDEX(basis!D$1:D$100;MATCH(A3;basis!A$1:A$100&" "&basis!B$1:B$100&basis!C$1:C100;0);1)

voer het te zoeken adres in als Blaak 7d (dus zonder verbindingsstreepjes)