Combinatie

Zoeken op rij en kolom met één X.ZOEKEN en NAAR.KOLOM

Vind de waarde waar een rijlabel en een kolomkop elkaar kruisen: je maakt van beide voorwaarden één raster met nullen en enen en zet dat met NAAR.KOLOM in één kolom.

Gebruikte functies:X.ZOEKEN NAAR.KOLOMExcel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)

Moeilijkheidsgraad

Uitgewerkt voorbeeld

De mutatie van één rekening in één periode

Situatie

Voor de cijferanalyse van Veldhuis Installatie BV heb je de mutaties per grootboekrekening voor de perioden 10 tot en met 12, uit de XAF-auditfile. Je wilt een cel waarin je een rekening en een periode typt en direct de mutatie ziet, zonder rijen of kolommen te tellen.

De gegevens

Fragment van het werkblad: in H3 staat de formule
Rij	A	B	C	D	G	H
1	Rekening	P10	P11	P12	Rekening	45004500
2	11001100	€48,100.00€ 48.100,00	€61,250.00€ 61.250,00	€70,300.00€ 70.300,00	Periode	P11
3	13001300	€22,500.00€ 22.500,00	€19,800.00€ 19.800,00	€26,400.00€ 26.400,00	Mutatie	€13,100.00€ 13.100,00=X.ZOEKEN(1, NAAR.KOLOM((H2=B1:D1)(H1=A2:A6)), NAAR.KOLOM(B2:D6), "Niet gevonden", 0)=X.ZOEKEN(1; NAAR.KOLOM((H2=B1:D1)(H1=A2:A6)); NAAR.KOLOM(B2:D6); "Niet gevonden"; 0)
4	40004000	€38,200.00€ 38.200,00	€38,900.00€ 38.900,00	€54,600.00€ 54.600,00
5	45004500	€12,500.00€ 12.500,00	€13,100.00€ 13.100,00	€18,750.00€ 18.750,00
6	80008000	€131,900.00€ 131.900,00	€128,300.00€ 128.300,00	€164,800.00€ 164.800,00
7

Formulecel (H3)Invoer

Formule in H3

=X.ZOEKEN(1, NAAR.KOLOM((H2=B1:D1)*(H1=A2:A6)), NAAR.KOLOM(B2:D6), "Niet gevonden", 0)

=X.ZOEKEN(1; NAAR.KOLOM((H2=B1:D1)*(H1=A2:A6)); NAAR.KOLOM(B2:D6); "Niet gevonden"; 0)

Resultaat: €13,100.00€ 13.100,00

Hoe de onderdelen samenwerken

Let op: Beide NAAR.KOLOM-functies moeten in dezelfde richting lezen, anders lopen de plekken uit elkaar en krijg je zonder waarschuwing een bedrag uit een andere cel.

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

```
=H2=B1:D1
```
```
=H2=B1:D1
```
Vergelijkt de periode in H2 met elke kolomkop. Het resultaat is één rij: ONWAAR, WAAR, ONWAAR. Alleen P11 komt overeen.
```
=H1=A2:A6
```
```
=H1=A2:A6
```
Vergelijkt de rekening in H1 met elk rijlabel. Het resultaat is één kolom van vijf waarden, met alleen WAAR op de vierde plek (rekening 4500).
```
=(H2=B1:D1)*(H1=A2:A6)
```
```
=(H2=B1:D1)*(H1=A2:A6)
```
Een rij keer een kolom geeft een raster zo groot als de bedragen: 5 rijen bij 3 kolommen. WAAR telt als 1 en ONWAAR als 0, dus het raster bevat één enkele 1, precies waar rekening en periode elkaar kruisen.
```
=NAAR.KOLOM((H2=B1:D1)*(H1=A2:A6))
```
```
=NAAR.KOLOM((H2=B1:D1)*(H1=A2:A6))
```
NAAR.KOLOM leest het raster rij voor rij en zet het onder elkaar in één kolom van 15 waarden. De 1 komt op plek 11: drie waarden per rij, drie volle rijen ervoor, en dan de tweede waarde van rij vier.
```
=NAAR.KOLOM(B2:D6)
```
```
=NAAR.KOLOM(B2:D6)
```
De bedragen in dezelfde volgorde in één kolom, rij voor rij. Op plek 11 staat 13.100, de mutatie van rekening 4500 in P11.
```
=X.ZOEKEN(1, NAAR.KOLOM((H2=B1:D1)*(H1=A2:A6)), NAAR.KOLOM(B2:D6), "Niet gevonden", 0)
```
```
=X.ZOEKEN(1; NAAR.KOLOM((H2=B1:D1)*(H1=A2:A6)); NAAR.KOLOM(B2:D6); "Niet gevonden"; 0)
```
X.ZOEKEN zoekt de 1 in het platgemaakte raster en geeft het bedrag op dezelfde plek terug. Komen rij en kolom nergens samen, dan toont het de tekst voor niet gevonden in plaats van #N/B.

Stap voor stap

H2=B1:D1 vergelijkt P11 met de koppen P10, P11 en P12: ONWAAR, WAAR, ONWAAR.
H1=A2:A6 vergelijkt 4500 met de rekeningen 1100, 1300, 4000, 4500 en 8000: ONWAAR, ONWAAR, ONWAAR, WAAR, ONWAAR.
De rij van drie keer de kolom van vijf geeft een raster van 5 bij 3. Elke cel is 0, behalve rij 4, kolom 2: 1 keer 1 = 1.
NAAR.KOLOM zet het raster rij voor rij onder elkaar: 15 waarden. De 1 staat op plek (4 - 1) x 3 + 2 = 11.
NAAR.KOLOM(B2:D6) zet de bedragen op dezelfde manier onder elkaar: 48,10048.100, 61,25061.250, 70,30070.300, 22,50022.500, enzovoort. Plek 11 is 13,10013.100.
X.ZOEKEN vindt de 1 op plek 11 en geeft het bedrag op plek 11 terug: 13,10013.100.

Waarom het werkt

De twee vergelijkingen beschrijven de rij en de kolom apart; door ze te vermenigvuldigen worden ze één raster waarin 1 betekent dat beide voorwaarden kloppen. Omdat beide NAAR.KOLOM-functies rij voor rij lezen, wijzen plek 11 in het raster en plek 11 in de bedragen naar dezelfde cel. X.ZOEKEN doet daarna een gewone zoekactie in één kolom.

Let op

Beide NAAR.KOLOM-functies moeten in dezelfde richting lezen. Laat je er maar één per kolom lezen (derde argument WAAR), dan lopen de plekken uit elkaar.
Staat de rekening in H1 als tekst ("4500" geplakt uit een rapport) en staan in kolom A getallen, dan komt geen enkele rij overeen en zie je de tekst voor niet gevonden. Zorg dat beide kanten hetzelfde type hebben.
Staat een rekening twee keer in de lijst, dan bevat het raster twee enen en geeft X.ZOEKEN de eerste terug. In een extract van de saldibalans is dat een teken dat je de export moet controleren.

Varianten

=X.ZOEKEN(H1, A2:A6, X.ZOEKEN(H2, B1:D1, B2:D6))

=X.ZOEKEN(H1; A2:A6; X.ZOEKEN(H2; B1:D1; B2:D6))

Geneste X.ZOEKEN: de binnenste geeft de hele kolom P11 terug, de buitenste kiest de rij van rekening 4500. Zelfde resultaat, vaak sneller te lezen als je per kant één sleutel hebt.

=INDEX(B2:D6, X.VERGELIJKEN(H1, A2:A6), X.VERGELIJKEN(H2, B1:D1))

=INDEX(B2:D6; X.VERGELIJKEN(H1; A2:A6); X.VERGELIJKEN(H2; B1:D1))

INDEX met twee posities uit X.VERGELIJKEN: rij 4, kolom 2. De klassieke manier om op rij en kolom te zoeken.

=SOM((H2=B1:D1)*(H1=A2:A6)*B2:D6)

=SOM((H2=B1:D1)*(H1=A2:A6)*B2:D6)

Hetzelfde raster keer de bedragen, daarna opgeteld. Ook dit geeft 13,10013.100, maar het telt dubbele rijen bij elkaar op in plaats van de eerste te nemen, en het werkt alleen voor getallen.

Alle combinaties