Gebruik liever X.ZOEKEN waar het kan. HORIZ.ZOEKEN werkt in elke versie van Excel. Heeft iedereen die het bestand gebruikt Excel 2021 of Microsoft 365, gebruik dan X.ZOEKEN.
Ga naar X.ZOEKENHORIZ.ZOEKEN
HLOOKUP in Engelstalige Excel
Zoekt een waarde in de bovenste rij van een tabel en geeft terug wat in dezelfde kolom staat, een opgegeven aantal rijen lager.
Laatst bijgewerkt: 13 oktober 2026
| Rij | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rekening | P1 | P2 | P3 | P4 | P5 | Periode | Bedrag | |
| 2 | Omzet | -€245,300.00€ -245.300,00 | -€251,870.50€ -251.870,50 | -€262,410.00€ -262.410,00 | -€238,950.25€ -238.950,25 | -€270,115.00€ -270.115,00 | P4 | €99,875.50€ 99.875,50=HORIZ.ZOEKEN(H2, A1:F4, 3, ONWAAR)=HORIZ.ZOEKEN(H2; A1:F4; 3; ONWAAR) | |
| 3 | Personeelskosten | €98,200.00€ 98.200,00 | €98,200.00€ 98.200,00 | €101,450.00€ 101.450,00 | €99,875.50€ 99.875,50 | €101,450.00€ 101.450,00 | |||
| 4 | Huisvestingskosten | €15,400.00€ 15.400,00 | €15,400.00€ 15.400,00 | €15,400.00€ 15.400,00 | €15,400.00€ 15.400,00 | €16,850.00€ 16.850,00 |
Veeg opzij om alle kolommen te zien
Resultaat: €99,875.50€ 99.875,50
=HORIZ.ZOEKEN(H2,A1:F4, 3, ONWAAR)
=HORIZ.ZOEKEN(H2;A1:F4; 3; ONWAAR)
Wat HORIZ.ZOEKEN doet en wanneer je het gebruikt
Wat het doet: het zoekt een sleutel in de bovenste rij van een tabel en geeft de waarde uit dezelfde kolom terug, een aantal rijen lager geteld, bijvoorbeeld de personeelskosten van één periode.
Wanneer je het gebruikt: als de perioden of codes bovenaan staan en het antwoord in een rij daaronder, vooral in bestanden die ook in Excel 2019 of ouder moeten werken. Het is de zijwaartse tegenhanger van VERT.ZOEKEN. X.ZOEKEN doet hetzelfde zonder de beperkingen, maar HORIZ.ZOEKEN zit nog in veel werkmappen.
Hoe Excel de functie berekent
Excel neemt de zoekwaarde en zoekt in de eerste rij van tabelmatrix, van links naar rechts. Met ONWAAR stopt het bij de eerste cel die gelijk is aan de sleutel; met WAAR gaat het ervan uit dat de rij gesorteerd is en kiest het de grootste sleutel die niet boven de zoekwaarde ligt.
Daarna telt het in diezelfde kolom rij_index_getal rijen omlaag vanaf de bovenrand van tabelmatrix en geeft het de cel terug waar het uitkomt. Komt geen enkele kolom in aanmerking, dan is het resultaat #N/B.
Let op: Zonder ONWAAR als vierde argument zoekt HORIZ.ZOEKEN benaderend, en een ontbrekende sleutel geeft stilletjes een naburige kolom in plaats van #N/B.
Denkmodel
Een liniaal die rechtop op een uitgeprinte tabel staat: je schuift hem langs de bovenste rij tot hij bij de sleutel staat, en leest dan langs de liniaal omlaag de rij af met het nummer dat je hebt gekregen.
HORIZ.ZOEKEN(zoekwaarde,tabelmatrix, rij_index_getal, [benaderen])
HORIZ.ZOEKEN(zoekwaarde;tabelmatrix; rij_index_getal; [benaderen])
| Argument | Soort | Betekenis | Standaard |
|---|---|---|---|
zoekwaarde | Verplicht | De waarde die je in de eerste rij van de tabel zoekt. | |
tabelmatrix | Verplicht | De tabel; de zoekrij moet de eerste rij zijn. | |
rij_index_getal | Verplicht | Welke rij van de tabel het antwoord bevat, geteld vanaf 1. | |
benaderen | Optioneel | ONWAAR voor een exacte overeenkomst; WAAR of weggelaten voor benaderend. | WAAR |
Geeft terug:De waarde in rij rij_index_getal van de gevonden kolom, of #N/B
Formules gebruiken komma's (,) tussen de argumenten, zoals in de Engelse Excel. Formules gebruiken puntkomma's (;) tussen de argumenten, zoals in de Nederlandse Excel. Formules gebruiken puntkomma's (;) tussen de argumenten, zoals in de Nederlandse Excel; de Engelse Excel gebruikt komma's (,).
Uitgewerkte voorbeelden
Beginner
Personeelskosten van één periode uit een maandoverzicht
In het grootboekoverzicht van de cliënt staan de perioden bovenaan (P1 tot en met P5) en eronder één regel per rekening. Je analyseert de mutatie in de personeelskosten en hebt het bedrag van periode P4 nodig voor je werkpapier. Je wilt een cel waarin je een periode typt en het bedrag verschijnt.
| Rij | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Rekening | P1 | P2 | P3 | P4 | P5 | Periode | Bedrag | ||
| 2 | Omzet | -€245,300.00€ -245.300,00 | -€251,870.50€ -251.870,50 | -€262,410.00€ -262.410,00 | -€238,950.25€ -238.950,25 | -€270,115.00€ -270.115,00 | P4 | €99,875.50€ 99.875,50=HORIZ.ZOEKEN(H2, A1:F4, 3, ONWAAR)=HORIZ.ZOEKEN(H2; A1:F4; 3; ONWAAR) | ||
| 3 | Personeelskosten | €98,200.00€ 98.200,00 | €98,200.00€ 98.200,00 | €101,450.00€ 101.450,00 | €99,875.50€ 99.875,50 | €101,450.00€ 101.450,00 | ||||
| 4 | Huisvestingskosten | €15,400.00€ 15.400,00 | €15,400.00€ 15.400,00 | €15,400.00€ 15.400,00 | €15,400.00€ 15.400,00 | €16,850.00€ 16.850,00 | ||||
| 5 |
Veeg opzij om alle kolommen te zien
Formule in I2
=HORIZ.ZOEKEN(H2,A1:F4, 3, ONWAAR)
=HORIZ.ZOEKEN(H2;A1:F4; 3; ONWAAR)
Resultaat: €99,875.50€ 99.875,50
Stap voor stap
- In
H2staat de tekstP4; dat is de zoekwaarde. - ONWAAR vraagt om een exacte overeenkomst, dus Excel loopt langs de eerste rij van
A1:F4: Rekening, nee; P1, nee; P2, nee; P3, nee; P4, ja. De overeenkomst zit in de vijfde kolom van de tabel, kolom E van het werkblad. - rij_index_getal is 3, dus Excel telt in die kolom drie rijen omlaag vanaf de bovenrand van de tabel:
E1is rij 1 (de sleutelrij),E2rij 2,E3rij 3. - De uitkomst is de waarde van
E3: 99,875.5099.875,50. Rij 3 van de tabel is de regel Personeelskosten, omdat de koprij als rij 1 telt. - Typ
P2inH2en het resultaat wordt 98,200.0098.200,00, de personeelskosten van periode 2, zonder dat je de formule aanpast.
Let op: Laat ONWAAR weg en typ P6 in H2: door de gesorteerde periodecodes komt de benaderde zoekopdracht uit bij P5 en het resultaat is 101,450.00101.450,00, voor een periode die niet in het overzicht staat. Met ONWAAR krijg je #N/B, en dat is het eerlijke antwoord.
Meer over dit voorbeeld: waarom het werkt, nog 1 valkuil
Waarom het werkt
De periodecode staat in de eerste rij van het geselecteerde blok, en dat is de enige plek waar HORIZ.ZOEKEN zoekt. Het rijnummer wordt binnen tabelmatrix geteld, niet vanaf rij 1 van het werkblad; begon het overzicht in rij 10, dan betekende 3 nog steeds de derde rij van het blok.
Meer om op te letten
- Staat er een spatie achter de codes in de bovenste rij ("P4 "), dan komt de
P4inH2nooit overeen en krijg je #N/B voor elke periode. Maak de koprij eerst schoon met SPATIES.WISSEN.
HalfgevorderdExcel 2013 of nieuwer (Dit voorbeeld vereist Excel 2013 of nieuwer)
Rente bij te late betaling per boekjaar, doorgekopieerd langs een lijst
De cliënt rekent rente bij te late betaling en het percentage verandert elk boekjaar. De tarieven staan naast elkaar, één kolom per jaar, met een rij voor standaardvoorwaarden en een rij voor grootafnemers.
Je herberekent het percentage naast elke achterstallige factuur en kopieert de formule naar beneden. Sommige oude facturen horen bij een jaar dat de tabel niet dekt, en die moeten dat melden in plaats van een foutwaarde te tonen.
| Rij | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Tarief | FY2022 | FY2023 | FY2024 | FY2025 | FY2026 | Factuur | Jaar | Percentage | ||
| 2 | Standaardvoorwaarden | 5%5% | 6.5%6,5% | 8%8% | 8.5%8,5% | 7.5%7,5% | 25-2041 | FY2024 | 8.0%8,0%=ALS.NB(HORIZ.ZOEKEN(I2, $A$1:$F$3, 2, ONWAAR), "Geen tarief in tabel")=ALS.NB(HORIZ.ZOEKEN(I2; $A$1:$F$3; 2; ONWAAR); "Geen tarief in tabel") | ||
| 3 | Grootafnemers | 4%4% | 5.5%5,5% | 7%7% | 7.5%7,5% | 6.5%6,5% | 25-2058 | FY2021 | |||
| 4 | 25-2063 | FY2026 | |||||||||
| 5 |
Veeg opzij om alle kolommen te zien
Formule in J2
=ALS.NB(HORIZ.ZOEKEN(I2,$A$1:$F$3, 2, ONWAAR), "Geen tarief in tabel")
=ALS.NB(HORIZ.ZOEKEN(I2;$A$1:$F$3; 2; ONWAAR); "Geen tarief in tabel")
Resultaat: 8.0%8,0%
Stap voor stap
- Excel begint bij HORIZ.ZOEKEN. In
I2staatFY2024: factuur 25-2041 hoort bij boekjaar 2024. - benaderen is ONWAAR, dus Excel loopt langs de eerste rij van
$A$1:$F$3: Tarief, nee; FY2022, nee; FY2023, nee; FY2024, ja. Dat is de vierde kolom van de tabel, kolom D. - rij_index_getal is 2, dus Excel geeft de cel een rij onder de sleutelrij in kolom D terug:
D2bevat 0,08, oftewel 8% voor de standaardvoorwaarden. - Het resultaat is geen #N/B, dus ALS.NB laat 0,08 ongewijzigd door.
- In
J3leest de gekopieerde formuleI3= FY2021. Geen enkele kop in de bovenste rij is gelijk aan FY2021, dus HORIZ.ZOEKEN geeft #N/B en ALS.NB vervangt dat door de tekst Geen tarief in tabel. Door de $-tekens bleef de tabel$A$1:$F$3, terwijlI2inI3veranderde. - In
J4staat FY2026 in de laatste kolom, F, dus het resultaat isF2: 0,075, oftewel 7,5%. Zonder ONWAAR zou een jaar als FY2027 uitkomen bij FY2026 en 7,5% tonen voor een jaar zonder tarief; met ONWAAR verschijnt de melding.
Let op: Laat je ONWAAR weg, dan krijgt een factuur uit FY2027 stilletjes het tarief van FY2026, omdat de gesorteerde koppen de benaderde zoekopdracht bij de laatste kolom laten uitkomen. Houd ONWAAR in elke tariefzoekopdracht op jaarcodes.
Meer over dit voorbeeld: waarom het werkt, nog 2 valkuilen, 1 variant
Waarom het werkt
Een exacte overeenkomst behandelt de bovenste rij als lijst met sleutels: een jaar staat er wel of niet in. ALS.NB reageert alleen op #N/B, en dat is precies het signaal voor een ontbrekend jaar, dus andere fouten zoals #VERW! blijven zichtbaar. De $-tekens zetten de tabel vast terwijl de zoekwaarde per kopie een rij omlaag gaat.
Meer om op te letten
- ALS.NB verbergt ook een typefout. Staat in
I2"FY 2024" of "FY2024 ", dan meldt de formule Geen tarief in tabel voor een jaar dat wel in de tabel staat. Controleer de jaarcellen met LENGTE als de melding onverwacht verschijnt. - Voeg je een rij in tussen de jaarkoppen en Standaardvoorwaarden, dan wijst de 2 naar de nieuwe regel in plaats van naar Standaardvoorwaarden. Tel de rijen opnieuw na elke wijziging in de tabel.
Varianten
=X.ZOEKEN(I2,$B$1:$F$1, $B$2:$F$2, "Geen tarief in tabel")
=X.ZOEKEN(I2;$B$1:$F$1; $B$2:$F$2; "Geen tarief in tabel")
De versie met X.ZOEKEN: de rij met jaren en de rij met tarieven zijn aparte argumenten, indien_niet_gevonden vervangt ALS.NB en er hoeft geen rijnummer geteld te worden.
GevorderdExcel 2013 of nieuwer (Dit voorbeeld vereist Excel 2013 of nieuwer)
Elke regel uit een begrotingstabel opvragen via het label
In de kostenbegroting van de cliënt staan de maanden bovenaan en als rijen de begroting, de werkelijke bedragen, de laatste prognose en het voorgaande jaar. In een zoekpaneel op je werkpapier kies je een maand en een regelnaam om met de bestuursnotulen te vergelijken. Het rijnummer moet het label volgen, zodat de formule blijft werken als de cliënt een regel aan de tabel toevoegt, en een onbekende maand of regel moet een duidelijke melding geven.
| Rij | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Regel | jan | feb | mrt | apr | mei | Maand | mrt | |
| 2 | Begroting | €42,000.00€ 42.000,00 | €42,000.00€ 42.000,00 | €45,500.00€ 45.500,00 | €45,500.00€ 45.500,00 | €47,000.00€ 47.000,00 | Regel | Werkelijk | |
| 3 | Werkelijk | €41,350.75€ 41.350,75 | €43,120.50€ 43.120,50 | €51,840.25€ 51.840,25 | €44,910.00€ 44.910,00 | €47,660.80€ 47.660,80 | Waarde | €51,840.25€ 51.840,25=ALS.NB(HORIZ.ZOEKEN(H1, $A$1:$F$5, VERGELIJKEN(H2, $A$1:$A$5, 0), ONWAAR), "Niet in begrotingstabel")=ALS.NB(HORIZ.ZOEKEN(H1; $A$1:$F$5; VERGELIJKEN(H2; $A$1:$A$5; 0); ONWAAR); "Niet in begrotingstabel") | |
| 4 | Laatste prognose | €42,000.00€ 42.000,00 | €43,500.00€ 43.500,00 | €51,000.00€ 51.000,00 | €45,000.00€ 45.000,00 | €47,500.00€ 47.500,00 | |||
| 5 | Voorgaand jaar | €38,900.00€ 38.900,00 | €39,450.00€ 39.450,00 | €41,200.00€ 41.200,00 | €42,780.00€ 42.780,00 | €43,100.00€ 43.100,00 | |||
| 6 |
Veeg opzij om alle kolommen te zien
Formule in H3
=ALS.NB(HORIZ.ZOEKEN(H1,$A$1:$F$5, VERGELIJKEN(H2, $A$1:$A$5, 0), ONWAAR), "Niet in begrotingstabel")
=ALS.NB(HORIZ.ZOEKEN(H1;$A$1:$F$5; VERGELIJKEN(H2; $A$1:$A$5; 0); ONWAAR); "Niet in begrotingstabel")
Resultaat: €51,840.25€ 51.840,25
Stap voor stap
- Excel begint bij de binnenste functie. VERGELIJKEN zoekt
H2, Werkelijk, in de labelkolom$A$1:$A$5met criteriumtype_getal 0: Regel, nee; Begroting, nee; Werkelijk, ja. Dat is de derde cel,A3, dus VERGELIJKEN geeft 3. - Die 3 wordt rij_index_getal. HORIZ.ZOEKEN zoekt nu
H1, mrt, in de eerste rij van$A$1:$F$5met ONWAAR: Regel, nee; jan, nee; feb, nee; mrt, ja. De overeenkomst zit in de vierde kolom van de tabel, kolom D. - HORIZ.ZOEKEN haalt de derde rij van die kolom op,
D3: 51,840.2551.840,25. - Het resultaat is geen #N/B, dus ALS.NB laat 51,840.2551.840,25 ongewijzigd door.
- Zet
H2op Begroting: VERGELIJKEN geeft 2 en het resultaat isD2, 45,500.0045.500,00. ZetH1op jun ofH2op Herprognose: een van de twee zoekopdrachten geeft #N/B, en ALS.NB vervangt dat door Niet in begrotingstabel. - Voeg tussen Begroting en Werkelijk een regel Herprognose Q1 in. Beide bereiken groeien naar rij 6, VERGELIJKEN vindt Werkelijk nu op positie 4, en de formule levert nog steeds 51,840.2551.840,25 op. Met een vaste 3 kreeg je nu de nieuwe regel.
Let op: Laat je de 0 in VERGELIJKEN weg, dan zoekt de functie benaderend in de labels, die niet gesorteerd zijn. Het kan een geloofwaardig maar fout rijnummer geven, en HORIZ.ZOEKEN geeft dan zonder foutmelding de verkeerde regel.
Meer over dit voorbeeld: waarom het werkt, nog 2 valkuilen, 2 varianten
Waarom het werkt
VERGELIJKEN maakt van een rijlabel het rijnummer dat HORIZ.ZOEKEN nodig heeft, zodat het zwakste onderdeel van HORIZ.ZOEKEN, de vaste rij, de gegevens volgt in plaats van de formule. Beide bereiken beginnen in rij 1; daardoor komen de positie van VERGELIJKEN en de rijtelling van HORIZ.ZOEKEN overeen. ALS.NB reageert alleen op #N/B, dus een kapotte verwijzing blijft zichtbaar als #VERW! in plaats van vermomd als onbekende regel.
Meer om op te letten
- Begint het labelbereik in rij 2 terwijl de tabel in rij 1 begint, dan zit elk resultaat één rij naast. Beide bereiken moeten in dezelfde rij beginnen.
- Eén melding dekt twee verschillende fouten. Toont het paneel Niet in begrotingstabel, dan weet je niet of de maand of de regel niet klopte, dus controleer beide cellen voordat je concludeert dat een bedrag ontbreekt.
Varianten
=ALS.NB(X.ZOEKEN(H1,$B$1:$F$1, X.ZOEKEN(H2, $A$2:$A$5, $B$2:$F$5)), "Niet in begrotingstabel")
=ALS.NB(X.ZOEKEN(H1;$B$1:$F$1; X.ZOEKEN(H2; $A$2:$A$5; $B$2:$F$5)); "Niet in begrotingstabel")
De versie met X.ZOEKEN: de binnenste X.ZOEKEN kiest de rij, de buitenste de kolom, en ALS.NB vangt nog steeds beide fouten op.
=ALS.NB(INDEX($B$2:$F$5,VERGELIJKEN(H2, $A$2:$A$5, 0), VERGELIJKEN(H1, $B$1:$F$1, 0)), "Niet in begrotingstabel")
=ALS.NB(INDEX($B$2:$F$5;VERGELIJKEN(H2; $A$2:$A$5; 0); VERGELIJKEN(H1; $B$1:$F$1; 0)); "Niet in begrotingstabel")
INDEX met twee keer VERGELIJKEN werkt in elke versie en kan ook een regel boven de sleutelrij teruggeven.
ExpertExcel 2021 of nieuwer (Dit voorbeeld vereist Excel 2021 of nieuwer)
Meerdere kostenregels van één kwartaal in één HORIZ.ZOEKEN
Het kostenrapport van de cliënt heeft de kwartalen bovenaan en daaronder de kostenregels: lonen, sociale lasten, pensioenpremies, huur en reiskosten.
Voor je cijferbeoordeling heb je de totale personeelskosten (de eerste drie regels) van een gekozen kwartaal nodig. Drie aparte HORIZ.ZOEKEN-formules werken, maar rij_index_getal accepteert ook een lijst met rijnummers, dus één HORIZ.ZOEKEN kan alle drie de regels in één keer ophalen.
| Rij | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Kostenregel | Q1 | Q2 | Q3 | Q4 | Kwartaal | Q3 | ||
| 2 | Lonen | €312,400.00€ 312.400,00 | €318,750.00€ 318.750,00 | €321,900.00€ 321.900,00 | €329,850.00€ 329.850,00 | Personeelskosten | €434,565.00€ 434.565,00=LET( kwartaal, H1, regels, {2,3,4}, ALS.NB(SOM(HORIZ.ZOEKEN(kwartaal, $A$1:$E$6, regels, ONWAAR)), "Kwartaal niet in tabel") )=LET( kwartaal; H1; regels; {2\3\4}; ALS.NB(SOM(HORIZ.ZOEKEN(kwartaal; $A$1:$E$6; regels; ONWAAR)); "Kwartaal niet in tabel") ) | ||
| 3 | Sociale lasten | €78,100.00€ 78.100,00 | €79,687.50€ 79.687,50 | €80,475.00€ 80.475,00 | €82,462.50€ 82.462,50 | ||||
| 4 | Pensioenpremies | €31,240.00€ 31.240,00 | €31,875.00€ 31.875,00 | €32,190.00€ 32.190,00 | €32,985.00€ 32.985,00 | ||||
| 5 | Huur | €45,600.00€ 45.600,00 | €45,600.00€ 45.600,00 | €45,600.00€ 45.600,00 | €45,600.00€ 45.600,00 | ||||
| 6 | Reiskosten | €12,480.00€ 12.480,00 | €15,920.00€ 15.920,00 | €11,260.00€ 11.260,00 | €14,190.00€ 14.190,00 | ||||
| 7 |
Veeg opzij om alle kolommen te zien
Formule in H2
=LET( kwartaal,H1, regels, {2, 3, 4}, ALS.NB(SOM(HORIZ.ZOEKEN(kwartaal, $A$1:$E$6, regels, ONWAAR)), "Kwartaal niet in tabel") )
=LET( kwartaal;H1; regels; {2\ 3\ 4}; ALS.NB(SOM(HORIZ.ZOEKEN(kwartaal; $A$1:$E$6; regels; ONWAAR)); "Kwartaal niet in tabel") )
Resultaat: €434,565.00€ 434.565,00
Stap voor stap
- LET geeft de invoer een naam: kwartaal wordt Q3 (uit
H1) en regels wordt de lijst 2, 3, 4. - HORIZ.ZOEKEN zoekt Q3 in de eerste rij van
$A$1:$E$6met ONWAAR: Kostenregel, nee; Q1, nee; Q2, nee; Q3, ja. De overeenkomst zit in de vierde kolom van de tabel, kolom D. - Omdat rij_index_getal een lijst is, leest Excel in dezelfde kolom één rij per getal: rij 2 geeft
D2= 321,900321.900; rij 3 geeftD3= 80,47580.475; rij 4 geeftD4= 32,19032.190. HORIZ.ZOEKEN geeft alle drie terug als matrix. - SOM telt de matrix op: 321,900321.900 + 80,47580.475 + 32,19032.190 = 434,565434.565.
- Er is geen #N/B, dus ALS.NB laat 434,565434.565 ongewijzigd door.
- Typ
Q4inH1en de drie waarden zijn 329,850329.850 + 82,462.5082.462,50 + 32,98532.985 = 445,297.50445.297,50. TypQ5: elk van de drie zoekopdrachten geeft #N/B, SOM geeft de fout door en ALS.NB toont Kwartaal niet in tabel.
Let op: Een veelgemaakte fout: ONWAAR weglaten. De gesorteerde kwartaalcodes laten een benaderde zoekopdracht bij Q4 uitkomen als je Q5 typt, dus de formule geeft het totaal van Q4 voor een kwartaal dat niet in het rapport staat, zonder foutmelding.
Meer over dit voorbeeld: waarom het werkt, nog 2 valkuilen, 2 varianten
Waarom het werkt
Omdat rij_index_getal een lijst is, geeft HORIZ.ZOEKEN uit de gevonden kolom één waarde per rijnummer terug, en SOM telt ze op. Microsoft beschrijft voor rij_index_getal alleen één getal; een lijst werkt, maar staat niet op de helppagina.
Gebruik deze opbouw niet in deze gevallen:
- het blok staat vast en je kunt het rechtstreeks optellen (SOM over de drie cellen van het kwartaal)
- er worden vaak regels toegevoegd of verplaatst, omdat de rijnummers posities zijn die de labels niet volgen
- de lezer moet in één oogopslag kunnen zien welke regels meetellen
De alternatieven zijn X.ZOEKEN, dat de hele kolom van het kwartaal teruggeeft zodat je de gewenste regels kunt optellen, en INDEX met VERGELIJKEN, dat hetzelfde doet in elke versie van Excel.
Meer om op te letten
- Voeg je een regel in boven Pensioenpremies, dan wijst de lijst 2, 3, 4 nog naar dezelfde posities, waardoor de nieuwe regel meetelt en Pensioenpremies uit het totaal valt. De uitkomst lijkt nog steeds een geloofwaardig bedrag. Controleer de lijst opnieuw na elke wijziging in de tabel.
- LET vereist Excel 2021 of Microsoft 365. Gebruik in oudere versies de INDEX-variant, die niet afhangt van LET of van matrixberekeningen.
Varianten
=ALS.NB(SOM(INDEX($B$2:$E$4,0, VERGELIJKEN(H1, $B$1:$E$1, 0))), "Kwartaal niet in tabel")
=ALS.NB(SOM(INDEX($B$2:$E$4;0; VERGELIJKEN(H1; $B$1:$E$1; 0))); "Kwartaal niet in tabel")
INDEX met rij_getal 0 geeft de hele kolom van het kwartaal voor de drie regels terug, en VERGELIJKEN zoekt de kolom. Het werkt in elke versie en de meegetelde regels zijn zichtbaar in het bereik B2:E4.
=ALS.NB(SOM(X.ZOEKEN(H1,$B$1:$E$1, $B$2:$E$4)), "Kwartaal niet in tabel")
=ALS.NB(SOM(X.ZOEKEN(H1;$B$1:$E$1; $B$2:$E$4)); "Kwartaal niet in tabel")
X.ZOEKEN met een rij sleutels en een blok van drie rijen geeft de drie waarden van de gevonden kolom terug, dus rijnummers zijn niet nodig. Het vereist Excel 2021 of later.
Modusverkenner
Typ een zoekwaarde, kies de rij die terugkomt en het argument benaderen en zie uit welke kolom HORIZ.ZOEKEN die haalt.
Rij die terugkomt
Bij benadering of exact
Bij benadering: de grootste waarde die niet boven de zoekwaarde ligt. De sleutelkolom moet oplopend zijn.
Exact: alleen een gelijke sleutel telt. De volgorde van de tabel maakt niet uit.
| Rij | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Klasse | 1 | 2 | 3 | 4 | 5 |
| 2 | Dagen te laat vanaf | 00 | 3030 | 6060 | 9090 | 120120 |
| 3 | Voorziening (%) | 0.50,5 | 22 | 1010 | 2525 | 5050 |
| 4 | ||||||
| 5 | Resultaat | 1010=HORIZ.ZOEKEN("x", $B$2:$F$3, 2, WAAR)=HORIZ.ZOEKEN("x"; $B$2:$F$3; 2; WAAR) |
Veeg opzij om alle kolommen te zien
- Formulecel
- Waar het zoeken uitkomt
- Wat het teruggeeft
=HORIZ.ZOEKEN(74, $B$2:$F$3, 2, WAAR)
=HORIZ.ZOEKEN(74; $B$2:$F$3; 2; WAAR)
Resultaat: 1010
74 staat niet in de bovenste rij. WAAR neemt de kolom van 60, de grootste waarde die er niet boven ligt: voorziening 10%.
=HORIZ.ZOEKEN(74, $B$2:$F$3, 2, ONWAAR)
=HORIZ.ZOEKEN(74; $B$2:$F$3; 2; ONWAAR)
Resultaat: #N/B
74 staat niet in de tabel, dus een exacte zoekopdracht vindt niets en geeft #N/B.
=HORIZ.ZOEKEN(74, $B$2:$F$3, 1, WAAR)
=HORIZ.ZOEKEN(74; $B$2:$F$3; 1; WAAR)
Resultaat: 6060
=HORIZ.ZOEKEN(74, $B$2:$F$3, 1, ONWAAR)
=HORIZ.ZOEKEN(74; $B$2:$F$3; 1; ONWAAR)
Resultaat: #N/B
Veelgemaakte fouten
| Symptoom | Oorzaak | Oplossing |
|---|---|---|
| Een naburige waarde in plaats van #N/B | Een ontbrekende periodecode geeft de waarde van een naburige kolom in plaats van #N/B, en niemand merkt het. | Zet ONWAAR (of 0) als laatste argument bij elke zoekopdracht op codes, perioden of namen. |
| De verkeerde regel na het invoegen van een rij | Er wordt een rij in de tabel ingevoegd en de formule geeft nu de verkeerde regel, omdat rij_index_getal nog steeds 3 is. | Laat VERGELIJKEN het rijnummer uit het rijlabel halen, zoals in het voorbeeld op niveau Gevorderd, of stap over op X.ZOEKEN, dat rechtstreeks naar de antwoordrij wijst. |
| De antwoordrij is niet te bereiken | De antwoordrij staat boven de sleutelrij, en geen enkel rij_index_getal komt daar. Een verwante slip is het rijnummer van het werkblad invullen in plaats van het rijnummer binnen de tabel. | Gebruik INDEX met VERGELIJKEN, of X.ZOEKEN, als het antwoord boven de sleutels staat. Tel de rijen vanaf de bovenkant van tabelmatrix, zodat de sleutelrij zelf 1 is. |
| Latere kopieën missen sleutels in de bovenste rij | De formule wordt zonder $ naar beneden of opzij gekopieerd, waardoor tabelmatrix bij elke kopie opschuift en latere kopieën sleutels in de bovenste rij van de tabel missen. | Schrijf de tabel als $A$1:$F$5, of maak er een tabel van en gebruik Tabel1[#Alles], dat de kopregel met de sleutels meeneemt. |