Excel links verticaal zoeken

Wie in Excel wel eens met de functie VERT.ZOEKEN (verticaal zoeken) gewerkt heeft, zal waarschijnlijk weten dat hiermee alleen waarden rechts van de zoekkolom te vinden zijn. Stel een Excel-sheet bevat de volgende gegevens:

links_verticaal_zoeken1_325x255

Wanneer nu bijvoorbeeld het rekeningnummer van iemand opgezocht moet worden, dan kan dat prima met de functie VERT.ZOEKEN. Als je bijvoorbeeld in bovenstaand voorbeeld in cel B10 een naam ingeeft, dan verschijnt in cel C10 automatisch het rekeningnummer als je in cel C10 de volgende formule ingeeft:

=VERT.ZOEKEN(B10;A2:C7;3;ONWAAR)

(NB: Vervang bij Engelstalige Excel-versies VERT.ZOEKEN door VLOOKUP en ONWAAR door FALSE)

Deze functie werkt als volgt: de ingegeven zoekwaarde in cel B10 wordt opgezocht in de eerste kolom van de matrix A2:C7, waarna de waarde uit de 3e kolom (is het rekeningnummer) van deze matrix wordt weergegeven. Het benaderen is ingesteld als ONWAAR, wat wil zeggen dat de functie alleen een resultaat geeft als de gevonden waarde in de eerste kolom van de matrix exact gelijk is aan de zoekwaarde.
Maar wat nu als je in bovenstaand overzicht het rekeningnummer in wilt geven en de bijbehorende naam wilt zoeken. Dit kan niet met VERT.ZOEKEN, omdat de gevonden waarde links van de zoekkolom staat. Eventueel kan wel gebruik worden gemaakt van een hulpkolom, maar het gebruik van een hulpkolom moet zoveel mogelijk vermeden worden. Een alternatief is om de volgende functie in te geven in cel C10:

=INDEX(A2:A7;VERGELIJKEN(B10;C2:C7;0))

(NB: Vervang bij Engelstalige Excel-versies VERGELIJKEN door MATCH)

Deze functie zorgt ervoor dat met de VERGELIJKEN de positie van de exacte zoekwaarde in de zoekkolom (C2:C7) gevonden wordt, waarna met behulp van de functie INDEX de waarde van dezelfde positie uit kolom A2:A7 wordt weergegeven. Op deze manier is het dus mogelijk om ook verticaal te zoeken links van de zoekkolom.

 

Interesse in andere tips & trucs? Ga dan naar ons tips&trucs-overzicht of neem een kijkje op onze pagina met tutorials.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

13 reacties op Excel links verticaal zoeken

  • Kun je vert.zoeken in combinatie met ALS functie?
    In de tweede kolom van de Matrix moet aan een bepaalde naam worden voldaan.

    • Bedankt voor je reactie.
      Er zijn meerdere mogelijkheden om dat te realiseren, maar dat is afhankelijk van de opbouw van de sheet. Hieronder een voorbeeld van een matrixformule (afsluiten met Ctrl+Shift+Enter) waarin in een voorwaarde is opgenomen in kolom B:
      =VERT.ZOEKEN(B10;ALS(B2:B7=”voorwaarde”;A2:C7);3;ONWAAR)

  • Heb volgend probleem: ik wil dat ik uit deze gegevens 1630 23 2365 456
    1633 23 2365 69
    1634 23 2365 457
    1635 23 2365 4
    die bv onder elkaar staan zoeken als de linkse waarde bv 1633 is dat hij me de rechtse waarde van 1, 2, 3 of 4 cijfers laat zien. Dus als 1633 23 2356 69 links 1633 is dan laat me 69 zien.

    • Hoi Jacky,
      Als de gegevens in A1:A10 staan en de zoekwaarde in B1, dan zou dat kunnen met de volgende matrixformule (ingeven met Ctrl+Shift+Enter):

      =ALS.FOUT(SPATIES.WISSEN(RECHTS(SUBSTITUEREN(INDEX(A1:A10;VERGELIJKEN(B1*1;LINKS(A1:A10;4)*1;0));" ";HERHALING(" ";100));100));"")

      Standaard worden bij deze functie alle karakters na de laatste spatie getoond. Het maakt niet uit of de zoekwaarde als getal of als een als tekst opgemaakt getal wordt ingevoerd.

      • Mooi, deze werkt opmoment dat ik (ingeven met Ctrl+Shift+Enter) doe maar daarna als ik gegevens wijzig blijft mijn cel leeg.

        • Gegevens wijzigen moet geen probleem zijn. De functie zoekt in de eerste vier posities van A1:A10 naar de zoekwaarde uit B1. Als deze zoekwaarde niet gevonden wordt in A1:A10, dan wordt een lege cel weergegeven.

          • Hay Manfred,

            Hoe kan ik deze lege cel vervangen door een 0?

            Bedankt alvast

            • Jacky, vervang de dubbele aanhalingstekens aan het einde van de formule door een 0 en je krijgt een 0 te zien in plaats van een lege cel als de zoekwaarde niet gevonden wordt.

  • Hi Manfred,
    Ik ben op zoek naar een functie waarbij ik in mijn excel bestand op de eerste pagina (jaarplanning) in andere tabbladen kan zoeken naar bijvoorbeeld alle weken 41 in kolom I en dat alle weken 41 die daarin voor komen het antwoord uit kolom L laat zien.

    Kolom I bestaat uit weeknummers die meerdere keren voorkomen.
    Kolom L is het resultaat als er uren gewerkt zijn in die week. Dat moet dus bij elkaar opgeteld worden.

    Heb het geprobeerd met verticaal zoeken, maar dat werkt helaas niet. Ik denk omdat ik graag de ‘som’ wil hebben van alle 1 betreffende week in die betreffende kolom.

    • Hoi Sanne,
      Hiervoor zou je de functie SOM.ALS kunnen gebruiken. Dit wordt dan iets als: =SOM.ALS(Bladnaam!I:I;41;Bladnaam!L:L)
      Omdat het blijkbaar gaat om meerdere tabbladen wordt het dan: =SOM.ALS(Bladnaam1!I:I;41;Bladnaam1!L:L)+SOM.ALS(Bladnaam2!I:I;41;Bladnaam2!L:L)+…

      • Hoi Manfred,
        Dank voor je reactie! Dit helpt me enorm!
        Nu is het alleen zo dat ik in kolom D namen heb staan van de personen die ‘eigenaar’ zijn van het betreffende project. Daar zou ik ook nog graag op willen zoeken, dus zeg: =SOM.ALS(Bladnaam!D:D;Sanne;I:I;41;Bladnaam!L:L) maar deze formule is helaas net te lang en pakt hij dus niet. Heb je daar wellicht een goede oplossing voor?
        Nogmaals ontzettend bedankt!

        • Hoi Sanne,
          In plaats van SOM.ALS kun je dan gebruik maken van SOMMEN.ALS. Het wordt dan: =SOMMEN.ALS(Bladnaam!L:L;Bladnaam!D:D;”Sanne”;Bladnaam!I:I;41)

          • Het werkt! Dank je wel!

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.