VERT.ZOEKEN met extra filteroptie in cel

[wtr2016]

Ik heb twee lijsten die ik wil combineren.

Lijst 1 bevat NAW gegevens,
Lijst 2 bevat de andere AW met in twee kolommen waardes (zeg bijv. autowaarde en kenteken).

In Lijst 1 is het adres geschreven als 'Steenweg 2, 1111 AA'
In Lijst 2 is het adres geschreven in aparte kolommen 'Steenweg', '2' en '1111 AA'

Nu wil ik de waarden uit lijst 2 combineren met lijst 2, maar heb ik geen losse waarde/veld voor het huisnummer.

Bestaat er een mogelijkheid om uit 'Steenweg 2' de laatste 2/3 karakters te halen en deze te matchen met de waardes uit lijst 2.
En het liefst middels verticaal zoeken op zowel twee waardes, namelijk postcode en huisnummer. (hoewel ik deze wel in een andere, nieuwe, kolom zou kunnen samenvoegen)

 

[JeanPaul28]

samenvoegen zonder spatie al geprobeerd

 

[Timshel]

Plaats even een helder voorbeeld.

 

[wtr2016]

In de bijlage het bestand.
Graag zou ik in de oranje kolom de waarde1 en waarde2 van het 'extra gegevens' werkblad willen hebben.

 

[Nelis1]

zoiets en dan met een index formule?
Bekijk bijlage Test-Excel-vertzoeken.xlsx

 

[Timshel]

@Nelis1. Dat gaat goed zolang huisnummers uit precies 2 cijfers bestaan en er bovendien alleen unieke straatnamen voorkomen in de adressentabel op tabblad 'extra gegevens'.
@wtr2016. Waarom zo'n summier voorbeeld met maar een adres? Denk je niet dat helpers een inschatting moeten kunnen maken van de gegevensopbouw in jouw bestand?
Om een beetje een robuuste oplossing te formuleren zijn m.i. ten minste 10 regels nodig.

 

[Nelis1]

HI Timshell en daar heb je helemaal gelijk in, dat is een belemmering (geen rekening met een flat gehouden )

 

[wtr2016]

Zoiets ja.
Ik heb het bestand een beetje uitgebreid.

Het resultaat zou ik graag zien als VERT.ZOEKEN of INDEX of een andere formule dat:
werkblad 'adresgegevens' de waarde toont van werkblad 'extra gegevens', namelijk in cel G2 123000; H2 20000; G3 90000 en H3 45000 etc

Namen en adressen etc zijn gefingeerd!

 

[Timshel]

Beter.
Als het om adressen gaat kun je het best postcode+huisnummer gebruiken als zoekindex. Straatnamen zijn gevoeliger voor typefouten.
In de bijlage vind je een mogelijke oplossing. Merk op dat in het voorbeeld geen match wordt gevonden voor Staalstraat 12 en 14 omdat de postcode niet overeenkomt (1011/1101).

 

[wtr2016]

Bedankt voor je reactie. Ik ga er morgen naar kijken.

 

[wtr2016]

Hallo Timshel,

Dit is inderdaad een goede manier. Thx.
Echter, heb ik een nieuw bestand toegevoegd, waar de juiste kolom-indeling inzit.

Het is me niet zo 1-2-3 gelukt om de formule aan te passen...
Kun jij hier nog even naar kijken en de juiste aanpassingen maken?

Op tabblad 'bestandX' betreft het de adres-kolommen en kolommen AF, AL en AR.

EDIT:
Het uiteindelijke bestand heeft iets van 29000 rijen

 

[Timshel]

In G2

=INDEX(bestandX!AF$2:AF$29000;VERGELIJKEN(SUBSTITUEREN($C2;" ";"")&DEEL($B2;ZOEKEN(1E+23;DEEL($B2;100-ROW(1:100);1)*1;100-RIJ(1:100));100);INDEX(bestandX!$H$2:$H$29000&bestandX!$J$2:$J$29000;0);0))

 

[wtr2016]

In G2

=INDEX(bestandX!AF$2:AF$29000;VERGELIJKEN(SUBSTITUEREN($C2;" ";"")&DEEL($B2;ZOEKEN(1E+23;DEEL($B2;100-ROW(1:100);1)*1;100-RIJ(1:100));100);INDEX(bestandX!$H$2:$H$29000&bestandX!$J$2:$J$29000;0);0))

Bedankt!

Echter, wanneer ik deze formule op het eerste werkblad 'adresgegevens' ingeef in G2 krijg ik #NAAM? te zien.
Het tabblad bestandX is nog niet gevuld met 29000 records. Kan het hiermee te maken hebben?
Of doe ik iets anders 'dat niet hoort'?

 

[Timshel]

Nee, de vertaling is niet helemaal goed gegaan.

=INDEX(bestandX!AF$2:AF$29000;VERGELIJKEN(SUBSTITUEREN($C2;" ";"")&DEEL($B2;ZOEKEN(1E+23;DEEL($B2;100-[COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]RIJ[/COLOR](1:100);1)*1;100-RIJ(1:100));100);INDEX(bestandX!$H$2:$H$29000&bestandX!$J$2:$J$29000;0);0))

 

[wtr2016]

Ik ben nu bezig met het bestand.
Op een of andere manier bij het 'doortrekken' van de formules, krijg ik geen waardes - #N/B - vanaf rij 88.

Kan dit kloppen? En hoe is dit te voorkomen, zodat er wel overal waardes getoond worden?

 

[Timshel]

=INDEX(bestandX!AF$2:AF$29000;VERGELIJKEN(SUBSTITUEREN($C2;" ";"")&DEEL($B2;ZOEKEN(1E+23;DEEL($B2;100-RIJ([COLOR="#FF0000"]$[/COLOR]1:[COLOR="#FF0000"]$[/COLOR]100);1)*1;100-RIJ([COLOR="#FF0000"]$[/COLOR]1:[COLOR="#FF0000"]$[/COLOR]100));100);INDEX(bestandX!$H$2:$H$29000&bestandX!$J$2:$J$29000;0);0))

My bad.

 

[wtr2016]

Om nog even terug te komen op deze vraag.

Achtergrond: vanuit een aantal bestanden/werkbladen heb ik een aantal gegevens nodig die gecombineerd moeten worden.


Ik heb dezelfde formule gebruikt in deze vraag.
Alleen, zie ik nu dat waardes niet correct worden overgenomen.

Het hoofd-werkblad die de gegevens overneemt van werkbladen 'bestandX' en 'bestandY' geeft in de groene cellen niet de juiste waarde.
Ligt er iets aan de formule?

Zie bestand voor voorbeeld.

 

[Timshel]

De eenvoudigste oplossing is Verticaal Zoeken op adres in kolom J. Hangt er wel van af hoe uniek die waarde is.