verticaal zoeken in tabel

[mariannevanh]

Ik wil in een tabel de functie VERT.ZOEKEN gebruiken. De ene keer moet gezocht worden op de code en dan weer op de naam.
Omdat de tabel - volgens mij - gesorteerd moet zijn op de zoeksleutel, kan dit volgens mij niet.
Wat zijn hiervoor de oplossingen?

Gedetailleerde info:
De bron is een in Excel omgezette tabel van deze tabel.
Van de opgeheven gemeenten wil ik weten hoe de huidige gemeente heet waaronder deze plaats valt.
Bijv. Duist kwam in 1857 onder Hoogland te vallen dat op zijn beurt onder Amersfoort valt.
(Voor mij is in dit geval het gegeven Amersfoort belangrijk).

 

[MirjamHelp]

Hoi Guidovanh,

Met een geneste formule zou je het gewenste resultaat kunnen bereiken. Dus een formule in een formule, omdat zo'n genest formule zijn nadelen heeft heb ik hier hulpkolommen ingeschakeld:

De basis is: Wanneer datum einde is ingevuld dan nieuwe code tonen en de anders plaatsnaam tonen.

ALS(VERT.ZOEKEN(oude code;tabel;datum einde;niet benaderen)>0;VERT.ZOEKEN(oude code;tabel;nieuwe code;niet benaderen);VERT.ZOEKEN(oude code;tabel;plaatsnaam;niet benaderen))

Kolom A	Kolom B	Kolom C	Kolom D	Kolom E	Kolom F	Kolom G	Kolom H
code	plaatsnaam	nieuwe code	datum ingang	datum einde	1e hulpkolom	2e hulpkolom	3e hulpkolom
0307	Amersfoort				Amersfoort	Amersfoort	Amersfoort
0320	Hoogland	0307		01-01-1974	0307	Amersfoort	Amersfoort
1314	Duist	0320		08-09-1857	0320	0307	Amersfoort

De formule in cel F2 (1e hulpkolom):

=ALS(VERT.ZOEKEN(A2;tabel;5;0)>0;VERT.ZOEKEN(A2;tabel;3;0);VERT.ZOEKEN(A2;tabel;2;0))

De formule in cel G2 (2e hulpkolom):

=ALS.NB(ALS(VERT.ZOEKEN(F2;tabel;5;0)>0;VERT.ZOEKEN(F2;tabel;3;0);VERT.ZOEKEN(F2;tabel;2;0));F2)

De formule in cel H2 (2e hulpkolom):

=ALS.NB(ALS(VERT.ZOEKEN(G2;tabel;5;0)>0;VERT.ZOEKEN(G2;tabel;3;0);VERT.ZOEKEN(G2;tabel;2;0));G2)

Mocht je meer hulp kolommen nodig hebben, dan gebruik je steeds dezelfde formule met verwijzing naar de kolom links ervan.

Hopelijk helpt dit je op weg.

Gr. Mirjam

NB: tabel verwijst dus naar je bereik waar de tabel staat. In dit voorbeeld $A$1:$A$4

 

[zapatr]

Gelet op het aantal gegevens waarmee rekening moet worden gehouden lijkt mij een vba-oplossing de meest aangewezen methode.

 

[mariannevanh]

NB: tabel verwijst dus naar je bereik waar de tabel staat. In dit voorbeeld $A$1:$A$4

Mirjam, dankjewel voor je uitgebreide hulp.
Als je verwijst naar $A$1:$A$4, hoe kun je dan gegevens halen uit de 5e kolom van de matrix?
Overigens wat is de formule als.nb. Die ken ik niet...

 

[MirjamHelp]

:shocked:Oeps... typ fout, moet natuurlijk kolom E zijn. En met CTRL-C / CTRL-V denk ik natuurlijk helemaal niet meer na...

En oh ja... formule in Excel 2013, even vergeten dat oudere versies deze formule nog niet hebben.

ALS.NB(waarde;waarde_als_nvt)

http://office.microsoft.com/nl-nl/excel-help/als-nb-functie-HA102753281.aspx

werkt hetzelfde als de formule voor foutwaarde die voor het eerst in Excel 2007 verscheen en maakt e.e.a. een stuk leesbaarder

ALS.FOUT(waarde;waarde_indien_fout)

Wanneer je geen XL2013 hebt kun je de formule in cel G2 (2e hulpkolom) vervangen door:

=ALS(ISNB(ALS(VERT.ZOEKEN(F2;tabel;5;0)>0;VERT.ZOEKEN(F2;tabel;3;0);VERT.ZOEKEN(F2;tabel;2;0)));"";ALS(VERT.ZOEKEN(F2;tabel;5;0)>0;VERT.ZOEKEN(F2;tabel;3;0);VERT.ZOEKEN(F2;tabel;2;0)))

gr. Mirjam

 

[mariannevanh]

Beste Mirjam,
Hartelijk dank voor je uitgebreide en heldere hulp.