VERT.ZOEKEN

Waarom het werkt

Het rekeningnummer is de eerste kolom van het geselecteerde blok, en dat is de enige plek waar VERT.ZOEKEN zoekt. Het kolomnummer wordt binnen tabelmatrix geteld, niet vanaf kolom A van het werkblad; begon de saldibalans in kolom D, dan betekende 3 nog steeds de derde kolom van het blok.

Meer om op te letten

• Staan de rekeningnummers in de export als tekst, dan komt het getal 4500 in E2 nooit overeen en krijg je #N/B voor elke rekening. Zet de kolom eerst om naar getallen.

Waarom het werkt

Benaderd zoeken beantwoordt de vraag 'in welke klasse valt dit?', omdat het de laatste klassegrens kiest die de waarde al gepasseerd is. Dat werkt alleen als de beginpunten van klein naar groot lopen; de sortering hoort bij de logica van de formule.

Meer om op te letten

• Een factuur die nog niet vervallen is, heeft een negatief aantal dagen te laat en geeft #N/B, omdat het onder het eerste beginpunt ligt. Daarom begint de tabel bij 0; test of filter die facturen eerst.

Varianten

=X.ZOEKEN(E2, $A$2:$A$6, $B$2:$B$6, , -1)

=X.ZOEKEN(E2; $A$2:$A$6; $B$2:$B$6; ; -1)

Waarom het werkt

VERGELIJKEN maakt van een kopnaam het kolomnummer dat VERT.ZOEKEN nodig heeft, zodat het zwakste onderdeel van VERT.ZOEKEN, de vaste kolom, de gegevens volgt in plaats van de formule. Beide bereiken beginnen in kolom A; daardoor komen de positie van VERGELIJKEN en de kolomtelling van VERT.ZOEKEN overeen. ALS.NB reageert alleen op #N/B, dus een kapotte verwijzing blijft zichtbaar als #VERW! in plaats van vermomd als onbekend activum.

Meer om op te letten

• Begint het kopbereik in kolom B terwijl de tabel in kolom A begint, dan zit elk resultaat één kolom naast. Beide bereiken moeten in dezelfde kolom beginnen.
• VERT.ZOEKEN geeft alleen de eerste overeenkomst. Komt een activanummer twee keer in de staat voor, dan wordt de tweede rij nooit gezien, en dat is op zich al een bevinding.

Varianten

=X.ZOEKEN(H1, $A$2:$A$6, X.ZOEKEN(H2, $A$1:$E$1, $A$2:$E$6), "Activum niet op de activastaat")

=X.ZOEKEN(H1; $A$2:$A$6; X.ZOEKEN(H2; $A$1:$E$1; $A$2:$E$6); "Activum niet op de activastaat")

=INDEX($A$2:$E$6, VERGELIJKEN(H1, $A$2:$A$6, 0), VERGELIJKEN(H2, $A$1:$E$1, 0))

=INDEX($A$2:$E$6; VERGELIJKEN(H1; $A$2:$A$6; 0); VERGELIJKEN(H2; $A$1:$E$1; 0))

Waarom het werkt

Benaderd zoeken in gesorteerde gegevens halveert bij elke vergelijking het zoekgebied, dus 200,000200.000 rijen kosten ongeveer 18 stappen in plaats van tot 200,000200.000. Door eerst kolom 1 terug te geven, krijg je het factuurnummer dat echt is gevonden, en door dat te vergelijken met het gevraagde nummer heb je weer de zekerheid van exact zoeken.

Gebruik deze opbouw niet op gegevens die niet betrouwbaar gesorteerd zijn, en spaar je de moeite bij kleine lijsten of in een actuele Microsoft 365, waar exact zoeken al veel sneller is dan vroeger: meet eerst, optimaliseer daarna.

Het alternatief is X.ZOEKEN met zoekmodus 2, dat binair zoeken en de bestaanscontrole in één functie doet, maar Excel 2021 of later vereist.

Meer om op te letten

• Eén ongesorteerde rij, bijvoorbeeld een late boeking die onderaan de export is toegevoegd, kan het binaire zoeken naar de verkeerde plek sturen. De ALS-controle meldt dan Niet in grootboek voor een factuur die er wel staat. Sorteer na elke import opnieuw.
• Factuurnummers die in de export als tekst staan en in F1 als getal, zijn nooit gelijk. Controleer de kolom met ISGETAL.

Varianten

=X.ZOEKEN(F1, A2:A8, C2:C8, "Niet in grootboek", 0, 2)

=X.ZOEKEN(F1; A2:A8; C2:C8; "Niet in grootboek"; 0; 2)