Vlookup-formule werkt alleen als de tabelmatrix in de formule niet verandert, maar als er een nieuwe kolom aan de tabel wordt 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 in dergelijke dynamische situaties gebruiken we de matchfunctie 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 referentiekolom 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.
- Lookup_value - 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 opgehaald 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 u niet naar de waarde uit de eerste kolom zoekt, wordt er een foutbericht "# N / A" geretourneerd.
# 2 - Wedstrijdformule
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.
- [match type] - Dit argument biedt drie opties, zoals hieronder 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 de lookup-array moet 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.
U kunt deze VLookup met Match Excel-sjabloon hier downloaden - VLookup met Match Excel-sjabloonBeschouw de onderstaande gegevenstabel die de specificaties van het gegeven voertuig beschrijft dat moet worden gekocht.
Om de duidelijkheid van de gecombineerde functie voor vlookup en match-functie te krijgen, laten we ons begrijpen hoe de individuele formule werkt en komen we bij het samenstellen van de vlookup-matchresultaten.
Stap # 1 - Laten we de vlookup-formule op individueel niveau toepassen om tot het resultaat te komen.
De output is hieronder weergegeven:
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", wat het aantal is voor kolom "Type" en het opzoeken van het bereik krijgt een exacte overeenkomst.
Daarom wordt de volgende formule toegepast om de waarde voor kolom "Brandstof" te retourneren .
De output is hieronder weergegeven:
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 is hieronder weergegeven:
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 is kolom 'Type' en het opzoekbereik dat moet worden doorzocht, wordt opgegeven als het bereik van kolomkoppen en het zoektype krijgt een exacte overeenkomst als ' 0 ”.
De onderstaande tabel geeft dus het gewenste resultaat voor de posities van de kolom "Brandstof".
Nu wordt hier de kolom waarnaar gezocht moet worden, cel D8 gegeven 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 is hieronder weergegeven:
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 is hieronder weergegeven:
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 ingevoerd in het tweede argument 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 matchfuncties zijn hoofdletterongevoelig bij het matchen van de lookup-waarde met de overeenkomende tekstwaarde in de tabelmatrix.
