Vlookup-formule werkt alleen als de tabelmatrix in de formule niet verandert, maar als er een nieuwe kolom aan de tabel is toegevoegd of als een kolom wordt verwijderd, geeft de formule een onjuist resultaat of geeft een fout weer, om de formule foutloos te maken in dergelijke dynamische situaties gebruiken we de match-functie om de index van de gegevens daadwerkelijk te matchen en het werkelijke resultaat te retourneren.
Combineer VERT.ZOEKEN met Match
De vlookup-formule is de meest gebruikte functie die wordt gebruikt om dezelfde waarde in de opgegeven kolomindex of de waarde uit een andere kolomindex te zoeken en te retourneren met verwijzing naar de overeenkomende waarde uit de eerste kolom. De grootste uitdaging bij het gebruik van vlookup is dat de kolomindex die moet worden gespecificeerd statisch is en geen dynamische functionaliteit heeft. Vooral als u aan meerdere criteria werkt, waarbij u de index van de referentiekolommen handmatig moet wijzigen. Daardoor wordt aan deze behoefte voldaan door de "MATCH" -formule te gebruiken om een betere grip of controle te hebben op de vaak veranderende kolomindex in de VERT.ZOEKEN-formule.
VLookup en Match-formule
# 1 - VERT.ZOEKEN Formule
De formule van de functie VERT.ZOEKEN in Excel
Hier zijn alle in te voeren argumenten verplicht.
- Opzoekwaarde - hier moet een referentiecel of tekst met dubbele aanhalingstekens worden ingevoerd om te worden geïdentificeerd in het kolombereik.
- Tabelmatrix - Dit argument vereist dat het tabelbereik wordt ingevoerd waar de Lookup_value moet worden doorzocht en de gegevens die moeten worden opgehaald, bevinden zich in het specifieke kolombereik.
- Col_index_num - In dit argument moet het kolomindexnummer of de telling van de kolom uit de eerste referentiekolom worden ingevoerd waaruit de corresponderende waarde moet worden gehaald uit dezelfde positie als de waarde die in de eerste kolom is gezocht.
- (Range_lookup) - Dit argument geeft twee opties.
- WAAR - Overeenkomst bij benadering: - Het argument kan worden ingevoerd als WAAR of numeriek "1", wat de overeenkomst bij benadering retourneert die overeenkomt met de referentiekolom of de eerste kolom. Bovendien moeten waarden in de eerste kolom van de tabelmatrix in oplopende volgorde worden gesorteerd.
- FALSE - Exacte overeenkomst: - Hier kan het in te voeren argument FALSE of numeriek "0" zijn. Deze optie retourneert alleen de exacte overeenkomst van de waarde die overeenkomt met de positie in het eerste kolombereik. Als de waarde uit de eerste kolom niet kon worden gezocht, zou er een foutmelding "# N / A" verschijnen.
# 2 - Wedstrijdformule
De match-functie retourneert de celpositie van de waarde die is ingevoerd voor de opgegeven tabelmatrix.
Alle argumenten in de syntaxis zijn verplicht.
- Lookup_value - Hier kan het ingevoerde argument de celverwijzing van de waarde zijn of een tekstreeks met dubbele aanhalingstekens waarvan de celpositie moet worden opgehaald.
- Lookup_array - Het matrixbereik voor de tabel moet worden ingevoerd waarvan de waarde of celinhoud moet worden geïdentificeerd.
- (zoektype) - Dit argument biedt drie opties, zoals hieronder wordt uitgelegd.
- "1-kleiner dan" - Hier is het in te voeren argument numeriek "1", die de waarde retourneert die kleiner is dan of gelijk is aan de opzoekwaarde. En ook moet de lookup-array in oplopende volgorde worden gesorteerd.
- "0-Exacte overeenkomst" - Hier moet het in te voeren argument numeriek "0" zijn. Deze optie retourneert de exacte positie van de overeenkomende opzoekwaarde. De opzoekmatrix kan echter in elke willekeurige volgorde staan.
- "-1-Groter dan" - Het in te voeren argument moet numeriek "-1" zijn. Met de derde optie wordt de kleinste waarde gevonden die groter is dan of gelijk is aan de opzoekwaarde. Hier moet de volgorde voor de lookup-array in aflopende volgorde worden geplaatst.
# 3 - VERT.ZOEKEN met MATCH-formule
= VERT.ZOEKEN (zoekwaarde, tabelmatrix, VERGELIJKEN (zoekwaarde, zoekmatrix, (match_type)), (bereik zoeken))
Hoe VERT.ZOEKEN te gebruiken met Match-formule in Excel?
Het onderstaande voorbeeld zal helpen bij het begrijpen van de werking van de vlookup en match-formule bij het samenstellen.
Beschouw de onderstaande gegevenstabel, die de specificaties van het betreffende aan te schaffen voertuig beschrijft.
Om de duidelijkheid van de gecombineerde functie voor vlookup en match-functie te krijgen, laten we ons begrijpen hoe de individuele formule werkt en vervolgens bij het samenstellen tot de vlookup-matchresultaten komen.
Stap # 1 - Laten we de vlookup-formule op individueel niveau toepassen om tot het resultaat te komen.
De output wordt hieronder getoond:
Hier wordt de opzoekwaarde verwezen naar $ B9, dat is model 'E', en de opzoekmatrix wordt gegeven als het bereik van de gegevenstabel met absolute waarde '$', de kolomindex wordt verwezen naar kolom '4', het aantal voor kolom "Type", en het opzoeken van het bereik krijgt een exacte overeenkomst.
Daarom wordt de volgende formule toegepast om de waarde voor de kolom 'Brandstof' te retourneren .
De output wordt hieronder getoond:
Hier helpt de opzoekwaarde met absolute tekenreeks “$” toegepast voor opzoekwaarde en lookup_array om de referentiecel te herstellen, zelfs als de formule naar een andere cel wordt gekopieerd. In de kolom "Brandstof" moeten we de kolomindex wijzigen in "5", aangezien de waarde waaruit de gegevens moeten worden opgehaald, verandert.
Stap # 2 - Laten we nu de Match-formule toepassen om de positie voor de opgegeven opzoekwaarde op te halen.
De output wordt hieronder getoond:
Zoals te zien is in de bovenstaande schermafbeelding, proberen we hier de kolompositie uit de tabelmatrix op te halen. In dit geval wordt het kolomnummer dat moet worden opgehaald, cel C8 genoemd, dat wil zeggen kolom 'Type', en het opzoekbereik dat moet worden doorzocht, wordt opgegeven als het bereik van kolomkoppen, en het zoektype krijgt een exacte overeenkomst om te worden als "0".
De onderstaande tabel geeft dus het gewenste resultaat voor de posities van de kolom 'Brandstof'.
Hier wordt de kolom waarnaar moet worden gezocht, cel D8, en de gewenste kolomindex wordt teruggegeven als "5".
Stap # 3 - Nu wordt de Match-formule gebruikt in de vlookup-functie om de waarde van de geïdentificeerde kolompositie te krijgen.
De output wordt hieronder getoond:
In de bovenstaande formule wordt de matchfunctie in plaats van de kolomindexparameter van de vlookup-functie geplaatst. Hier identificeert de match-functie de referentiecel "C8" van de opzoekwaarde en retourneert het kolomnummer via de gegeven tabelmatrix. Deze kolompositie dient als invoer voor het kolomindexargument in de vlookup-functie. Wat zal op zijn beurt vlookup helpen om de waarde te identificeren die moet worden geretourneerd uit het resulterende kolomindexnummer?
Evenzo hebben we vlookup met matchformule ook voor de kolom "Brandstof" toegepast.
De output wordt hieronder getoond:
Hierdoor kunnen we deze combinatiefunctie ook voor andere kolommen “Type” en “Brandstof” toepassen.
Dingen om te onthouden
- VERT.ZOEKEN kan alleen worden toegepast op opzoekwaarden in de meest linkse kant. Alle waarden die aanwezig zijn om te worden doorzocht aan de rechterkant van de gegevenstabel, retourneren de foutwaarde "# N / A".
- Het bereik van table_array dat in het tweede argument wordt ingevoerd, moet de absolute celverwijzing “$” zijn, hierdoor blijft het vaste tabelmatrixbereik behouden wanneer de opzoekformule op andere cellen wordt toegepast, anders zullen de referentiecellen voor het tabelmatrixbereik naar de volgende cel verschuiven referentie.
- De waarde die is ingevoerd in de opzoekwaarde mag niet kleiner zijn dan de kleinste waarde in de eerste kolom van de tabelmatrix, anders retourneert de functie de foutwaarde "# N / A".
- Voordat u een geschatte overeenkomst "TRUE" of "1" toepast in het laatste argument, moet u er altijd aan denken om de tabelarray in oplopende volgorde te sorteren.
- De match-functie retourneert alleen de positie van de waarde in de vlookup-tabelmatrix en retourneert niet de waarde.
- In het geval dat de Match Function de positie van de opzoekwaarde in de tabelmatrix niet kan identificeren, retourneert de formule "# N / A" in de foutwaarde.
- Vlookup- en match-functies zijn niet hoofdlettergevoelig bij het matchen van de lookup-waarde met de overeenkomende tekstwaarde in de tabelmatrix.
