Vlookup-formlen fungerer kun, når tabelarrayet i formlen ikke ændres, men hvis der er indsat en ny kolonne i tabellen, eller hvis en kolonne slettes, giver formlen et forkert resultat eller reflekterer en fejl for at gøre formlen fejlfri i sådanne dynamiske situationer bruger vi matchfunktion til faktisk at matche dataindekset og returnere det faktiske resultat.
Kombiner VLOOKUP med Match
Vlookup-formlen er den mest anvendte funktion, der bruges til at søge og returnere enten den samme værdi i det angivne kolonneindeks eller værdien fra et andet kolonneindeks med reference til den matchede værdi fra den første kolonne. Den største udfordring, når du bruger vlookup, er, at det kolonneindeks, der skal specificeres, er statisk og ikke har en dynamisk funktionalitet. Især når du arbejder på flere kriterier, der kræver, at du ændrer referencekolonneindekset manuelt. Derved opfyldes dette behov ved at bruge formlen “MATCH” til at få et bedre greb eller kontrol med det ofte skiftende kolonneindeks i VLOOKUP-formlen.
VLookup og Match Formula
# 1 - VLOOKUP-formel
Formlen til VLOOKUP-funktion i Excel
Her er alle argumenter, der skal indtastes, obligatoriske.
- Opslagsværdi - Her skal der angives referencecelle eller tekst med dobbelt anførselstegn for at blive identificeret i kolonneområdet.
- Tabel matrix - Dette argument kræver tabelområdet der skal optages, når den opslagsværdi bør søges og de data, der skal hentes bosiddende i bestemt kolonne rækkevidde.
- Col_index_num - I dette argument skal kolonneindeksnummeret eller antallet af kolonnen fra den første kolonne henvises, hvorfra den tilsvarende værdi skal trækkes fra samme position som den værdi, der blev søgt i den første kolonne.
- [Range_lookup] - Dette argument giver to muligheder.
- TRUE - Omtrentlig match: - Argumentet kan enten indtastes som TRUE eller numerisk "1", som returnerer det omtrentlige match, der svarer til referencekolonnen eller den første kolonne. Desuden skal værdier i den første kolonne i tabelarray sorteres i stigende rækkefølge.
- FALSE - Eksakt match: - Her kan argumentet, der skal indtastes, enten være FALSE eller numerisk "0". Denne mulighed returnerer kun det nøjagtige match for den værdi, der svarer til at blive identificeret, fra positionen i det første kolonneområde. Manglende søgning efter værdien fra den første kolonne returnerer en "# N / A" -fejlmeddelelse.
# 2 - Matchformel
Matchfunktion returnerer cellepositionen for den indtastede værdi for det givne tabelarray.
Alle argumenter inden for syntaksen er obligatoriske.
- Lookup_value - Her kan det indtastede argument enten være cellereferencen for værdien eller en tekststreng med dobbelt anførselstegn, hvis celleposition skal trækkes.
- Lookup_array - Matrixområdet for tabellen skal indtastes, hvis værdi eller celleindhold ønskes identificeret.
- [matchtype] - Dette argument giver tre muligheder som forklaret nedenfor.
- "1-Mindre end" - Her er det argument, der skal indtastes, det numeriske "1", som returnerer den værdi, der er mindre end eller lig med opslagsværdien. Og også opslagsarrayet skal sorteres i stigende rækkefølge.
- “0-nøjagtig match” - Her skal argumentet, der skal indtastes, være det numeriske “0”. Denne mulighed returnerer den nøjagtige placering af den matchede opslagsværdi. Opslagsarrayet kan dog være i enhver rækkefølge.
- “-1-Større end” - Argumentet, der skal indtastes, skal være numerisk “-1”. Den tredje mulighed finder den mindste værdi, der er større end eller lig med opslagsværdien. Her skal rækkefølgen for opslagsarray placeres i faldende rækkefølge.
# 3 - VLOOKUP med MATCH-formel
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])
Hvordan bruges VLOOKUP med Match Formula i Excel?
Eksemplet nedenfor hjælper med at forstå, hvordan vlookup- og matchformlen fungerer, når du sammensætter.
Du kan downloade denne VLookup med Match Excel-skabelon her - VLookup med Match Excel-skabelonOvervej nedenstående datatabel, der beskriver specifikationerne for det givne køretøj, der skal købes.
For at få klarheden i den kombinerede funktion til vlookup og matchfunktion, lad os forstå, hvordan fungerer den enkelte formel og derefter nå frem til vlookup-matchresultaterne, når de sammensættes.
Trin # 1 - Lad os anvende vlookup-formlen på et individuelt niveau for at nå frem til resultatet.
Outputtet er vist nedenfor:
Her henvises opslagsværdien til $ B9, der er model "E", og opslagsarrayen er angivet som datatabellens rækkevidde med absolut værdi "$", kolonneindekset henvises til kolonne "4", som er antallet af kolonne "Type", og rækkeviddeopslaget gives et nøjagtigt match.
Således anvendes følgende formel til returnering af værdien for kolonne "Brændstof".
Outputtet er vist nedenfor:
Her finder opslagsværdien med absolut streng "$" anvendt til opslagsværdi og opslagsarray til at rette referencecellen, selvom formlen kopieres til en anden celle. I kolonnen "Brændstof" skal vi ændre kolonneindekset til "5", da den værdi, hvorfra dataene er nødvendige for at blive hentet, ændres.
Trin # 2 - Lad os nu anvende matchformlen for at hente positionen for den givne opslagsværdi.
Outputtet er vist nedenfor:
Som det kan ses i ovenstående skærmbillede, prøver vi her at hente søjlepositionen fra tabelarrayet. I dette tilfælde kaldes det kolonnetal, der skal trækkes, som celle C8, der er kolonne "Type", og det opslagsområde, der skal søges, gives som området for kolonneoverskrifter, og matchtypen får en nøjagtig matchning, der skal være som " 0 ”.
Således giver nedenstående tabel det ønskede resultat for positioner i kolonne "Brændstof".
Her får kolonnen, der skal søges, til at være celle D8, og det ønskede kolonneindeks returneres til at være "5".
Trin # 3 - Nu vil matchformlen blive brugt i vlookup-funktionen for at hente værdien fra den identificerede kolonneposition.
Outputtet er vist nedenfor:
I ovenstående formel placeres matchningsfunktionen i stedet for kolonneindeksparameteren for vlookup-funktionen. Her identificerer matchfunktionen referencecellen for opslagsværdi "C8" og returnerer kolonnetallet gennem det givne tabelarray. Denne kolonneposition vil tjene formålet som input til kolonneindeksargumentet i vlookup-funktionen. Hvilket vil igen hjælpe vlookup med at identificere den værdi, der skal returneres fra det resulterende kolonneindeksnummer?
På samme måde har vi også anvendt vlookup med matchformel for kolonnen "Brændstof".
Outputtet er vist nedenfor:
Vi kan derved anvende denne kombinationsfunktion også til andre kolonner "Type" og "Brændstof".
Ting at huske
- VLOOKUP kan kun anvendes til opslagsværdier i den forreste venstre side. Alle tilstedeværende værdier, der skal søges i højre side af datatabellen, returnerer fejlværdien “# N / A”.
- Området for table_array, der er indtastet i det andet argument, skal være absolut cellehenvisning "$", dette opretholder det faste tabelarrayområde, når opslagsformlen anvendes på andre celler, ellers skifter referencecellerne for tabelarrayområdet til den næste celle reference.
- Den værdi, der er indtastet i opslagsværdien, bør ikke være mindre end den mindste værdi i den første kolonne i tabelarrayet, ellers returnerer funktionen "# N / A" -fejlværdien.
- Inden du anvender et omtrentligt match "SAND" eller "1" i det sidste argument, skal du altid huske at sortere tabelarrayet i stigende rækkefølge.
- Matchfunktionen returnerer kun positionen for værdien i vlookup-tabelarrayet og returnerer ikke værdien.
- I tilfælde af, at Match-funktion ikke er i stand til at identificere placeringen af opslagsværdien i tabelarrayet, returnerer formlen "# N / A" i fejlværdien.
- Vlookup og matchfunktioner er ikke store og små bogstaver, når de matcher opslagsværdien med den matchende tekstværdi i tabelarrayet.