De VERT.ZOEKEN, HORIZ.ZOEKEN en INDEX-MATCH zijn bekende en veelgebruikte manieren om waarden op te zoeken in de Excel-tabel. Maar dit zijn niet de enige manieren om waarden op te zoeken in Excel. In dit artikel zullen we leren hoe we de OFFSET-functie samen met de MATCH-functie in Excel kunnen gebruiken. Ja, je leest het goed, we zullen de beruchte OFFSET-functie van Excel gebruiken om een bepaalde waarde op te zoeken in een Excel-tabel.
Generieke formule,
| =OFFSET(StartCell,MATCH(RowLookupValue,RowLookupRange,0),MATCH(ColLookupValue,ColLookupRange,0)) |
Lees dit goed door:
StartCel:Dit is de startcel van de opzoektabel. Laten we zeggen dat als u wilt opzoeken in het bereik A2:A10, de StartCell A1 zal zijn.
RowLookupWaarde: Dit is de opzoekwaarde die u wilt vinden in rijen onder deStartCel.
RijLookupBereik:Dit is het bereik waarin u de RowLookupValue wilt opzoeken. Het is het bereik hieronder: StartCell (A2:A10).
ColLookupWaarde: Dit is de opzoekwaarde die u in kolommen (headers) wilt vinden.
ColLookupBereik:Dit is het bereik waarin u de ColLookupValue wilt opzoeken. Het is het bereik aan de rechterkant van StartCell (zoals B1:D1).
Zo, genoeg van de theorie. Laten we beginnen met een voorbeeld.
Voorbeeld: Verkoop opzoeken met de functie OFFSET en MATCH uit Excel-tabel
Hier hebben we een voorbeeldtabel met verkopen die door verschillende werknemers in verschillende maanden zijn gedaan.
Nu vraagt onze baas ons om de verkopen te geven die zijn gedaan door Id 1004 in de juni-maand. Er zijn veel manieren om dit te doen, maar aangezien we de OFFSET-MATCH-formule leren kennen, zullen we ze gebruiken om de gewenste waarde op te zoeken.
We hebben al de generieke formule hierboven. We hoeven alleen deze variabelen in deze tabel te identificeren.
StartCell is hier B1. RijLookupWaarde is M1. RijLookupBereik is B2:B1o (bereik onder de startcel).
ColLookupWaarde bevindt zich in M2-cel. ColLookupBereik is C1:I1 (Headerbereik aan de rechterkant van de StartCell) .
We hebben alle variabelen geïdentificeerd. Laten we ze in een Excel-formule plaatsen.
Schrijf deze formule in Cell M3 en druk op de enter-knop.
| =OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0)) |
Als je op de enter-knop drukt, krijg je het resultaat onmiddellijk. De verkoop gedaan ID 1004 in juni maand is 177.
Hoe werkt het?
Het werk van de OFFSET-functie is om van de gegeven startcel naar de gegeven rij en kolommen te gaan en vervolgens de waarde uit die cel terug te geven. Als ik bijvoorbeeld OFFSET(B1,1,1) schrijf, gaat de OFFSET-functie naar cel C2 (1 cel omlaag, 1 cel naar rechts) en retourneert deze de waarde.
Hier hebben we formule:OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0)).
De eerste MATCH-functie retourneert de index van M1 (1004) in bereik B2:B10, dat is 4. Nu is de formule,OFFSET(B1,4,MATCH(M2,C1:I1,0)).
Volgende MATCH-functie retourneert de index van M2 ('Jun') in bereik C1:I1, die i 7. Nu is de formuleVERSCHUIVING(B1,4,7).
Nu verplaatst de OFFSET-functie eenvoudig 4 cellen naar de cel B1 die deze naar B5 brengt. Dan gaat de OFFSET-functie naar 7 links naar B5, die deze naar I5 brengt. Dat is het. De functie OFFSET retourneert een waarde uit cel I5, die 177.
Voordelen van deze opzoektechniek?
Dit is snel. Het enige voordeel van deze methode is dat deze sneller is dan de andere methoden. Hetzelfde kan worden gedaan met behulp van de INDEX-MATCH-functie of de VERT.ZOEKEN-functie. Maar het is goed om enkele alternatieven te kennen. Het kan ooit van pas komen.
Dus ja jongens, dit is hoe je de OFFSET- en MATCH-functie kunt gebruiken om waarden op te zoeken in Excel-tabellen. Ik hoop dat het voldoende verhelderend was. Als je twijfels hebt over dit artikel of een ander Excel/VBA-gerelateerd onderwerp, vraag het me dan in de comments hieronder.
Gebruik INDEX en MATCH om waarde op te zoeken:De formule INDEX-MATCH wordt gebruikt om dynamisch en nauwkeurig een waarde in een bepaalde tabel op te zoeken. Dit is een alternatief voor de functie VERT.ZOEKEN en lost de tekortkomingen van de functie VERT.ZOEKEN op.
Som door OFFSET-groepen in rijen en kolommen: De OFFSET-functie kan worden gebruikt om groepen cellen dynamisch op te tellen. Deze groepen kunnen overal in het blad staan.
Hoe u elke N-de waarde in een bereik in Excel kunt ophalen: Met behulp van de OFFSET-functie van Excel kunnen we waarden ophalen uit afwisselende rijen of kolommen. Deze formule maakt gebruik van de ROW-functie om door rijen te wisselen en de COLUMN-functie om in kolommen te wisselen.
Hoe de OFFSET-functie in Excel te gebruiken: De OFFSET-functie is een krachtige Excel-functie die door veel gebruikers wordt onderschat. Maar experts kennen de kracht van de OFFSET-functie en hoe kunnen we deze functie gebruiken om enkele magische taken in de Excel-formule uit te voeren. Hier zijn de basisprincipes van de OFFSET-functie.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de Excel VERT.ZOEKEN-functie te gebruiken| Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de Excel te gebruiken? AANTAL.ALS-functie| Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. De AANTAL.ALS-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.