VLOOKUP med MATCH | Lag fleksibel formel med VLOOKUP MATCH

Vlookup-formelen fungerer bare når tabellmatrisen i formelen ikke endres, men hvis det er satt inn en ny kolonne i tabellen eller en kolonne blir slettet, gir formelen et feil resultat eller gjenspeiler en feil, for å gjøre formelen feilfri i slike dynamiske situasjoner bruker vi matchfunksjon for å faktisk matche indeksen til dataene og returnere det faktiske resultatet.

Kombiner VLOOKUP med Match

Vlookup-formelen er den mest brukte funksjonen som brukes til å søke og returnere enten den samme verdien i den angitte kolonneindeksen eller verdien fra en annen kolonneindeks med referanse til den samsvarende verdien fra den første kolonnen. Den største utfordringen når du bruker vlookup er at kolonneindeksen som skal spesifiseres er statisk og ikke har en dynamisk funksjonalitet. Spesielt når du jobber med flere kriterier som krever at du endrer referanse kolonneindeksen manuelt. Dermed oppfylles dette behovet ved å bruke "MATCH" -formelen for å få bedre grep eller kontroll over den ofte skiftende kolonneindeksen i VLOOKUP-formelen.

VLookup og Match Formula

# 1 - VLOOKUP Formula

Formelen for VLOOKUP-funksjonen i Excel

Her er alle argumentene som skal legges inn obligatoriske.

  • Lookup_value - Her skal referansecelle eller tekst med doble anførselstegn angis for å bli identifisert i kolonneområdet.
  • Tabellmatrise -   Dette argumentet krever at tabellområdet skal legges inn der Lookup_value skal søkes, og dataene som skal hentes ligger i det aktuelle kolonneområdet.
  • Col_index_num - I dette argumentet må kolonneindeksnummeret eller antall kolonner fra referanse første kolonne legges inn hvorfra den tilsvarende verdien må trekkes fra samme posisjon som verdien som ble søkt i den første kolonnen.
  • [Range_lookup] - Dette argumentet vil gi to alternativer.
  • SANN - Omtrentlig samsvar: - Argumentet kan enten legges inn som SANT eller numerisk “1”, som returnerer den omtrentlige treff som tilsvarer referansekolonnen eller første kolonne. Videre må verdiene i den første kolonnen i tabellmatrisen sorteres i stigende rekkefølge.
  • FALSE - Nøyaktig samsvar: - Her kan argumentet som skal angis enten være FALSE eller numerisk "0". Dette alternativet vil bare returnere den nøyaktige samsvaret med verdien som tilsvarer å bli identifisert fra posisjonen i det første kolonneområdet. Unnlatelse av å søke i verdien fra den første kolonnen vil returnere feilmeldingen “# N / A”.

# 2 - Kampformel

Match-funksjon returnerer celleposisjonen til verdien som er angitt for den gitte tabellmatrisen.

Alle argumentene innenfor syntaksen er obligatoriske.

  • Lookup_value - Her kan det angitte argumentet være enten cellereferansen til verdien eller en tekststreng med doble anførselstegn der det kreves at celleposisjonen trekkes.
  • Lookup_array - Det kreves at matrixområdet for tabellen legges inn hvis verdi eller celleinnhold ønskes identifisert.
  • [samsvarstype] - Dette argumentet gir tre alternativer som forklart nedenfor.
  • “1-Mindre enn” - Her er argumentet som skal angis, numerisk “1” som returnerer verdien som er mindre enn eller lik oppslagsverdien. Og oppslagsmatrisen må også sorteres i stigende rekkefølge.
  • “0-nøyaktig samsvar” - Her skal argumentet som skal angis være numerisk “0”. Dette alternativet returnerer den nøyaktige plasseringen av den matchede oppslagsverdien. Oppslagsmatrisen kan imidlertid være i hvilken som helst rekkefølge.
  • “-1-Større enn” -  Argumentet som skal angis, skal være numerisk “-1”. Det tredje alternativet finner den minste verdien som er større enn eller lik oppslagsverdien. Her må rekkefølgen for oppslagsmatrisen plasseres i synkende rekkefølge.

# 3 - VLOOKUP med MATCH Formula

= VISNING (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])

Hvordan bruker jeg VLOOKUP med Match Formula i Excel?

Eksemplet nedenfor hjelper deg med å forstå funksjonen til vlookup og matchformelen når du setter sammen.

Du kan laste ned denne VLookup med Match Excel-mal her - VLookup med Match Excel Template

Vurder nedenstående datatabell som beskriver spesifikasjonene for det gitte kjøretøyet som skal kjøpes.

For å få klarheten i den kombinerte funksjonen for vlookup og match-funksjon, la oss forstå hvordan den enkelte formelen fungerer, og deretter komme til resultatene for vlookup-match når den settes sammen.

Trinn 1 - La oss bruke vlookup-formelen på et individuelt nivå for å komme til resultatet.

Resultatet er vist nedenfor:

Her henvises oppslagsverdien til $ B9 som er modell "E" og oppslagsmatrisen er gitt som datatabellens område med absolutt verdi "$", kolonneindeksen henvises til kolonne "4" som er tellingen for kolonne “Type” og områdesøk blir gitt en nøyaktig samsvar.

Dermed brukes følgende formel for å returnere verdien for kolonnen "Drivstoff".

Resultatet er vist nedenfor:

Her søker oppslagsverdien med absolutt streng “$” for oppslagsverdi og oppslag_array med å fikse referansecellen selv om formelen kopieres til en annen celle. I "Fuel" -kolonnen, må vi endre kolonneindeksen til "5" da verdien som dataene er nødvendige for å bli hentet fra endres.

Trinn 2 -  La oss nå bruke formelen for å hente posisjonen for den oppgitte oppslagsverdien.

Resultatet er vist nedenfor:

Som det kan sees i skjermbildet ovenfor, prøver vi her å hente kolonneposisjonen fra tabellmatrisen. I dette tilfellet blir kolonnetallet som skal trekkes referert til som celle C8 som er kolonne "Type", og oppslagsområdet som skal søkes, blir gitt som området kolonneoverskrifter, og samsvarstypen får en nøyaktig samsvar for å være som " 0 ”.

Dermed vil tabellen nedenfor gi ønsket resultat for posisjoner i kolonne "Drivstoff".

Nå er kolonnen som skal søkes gitt til celle D8 og ønsket kolonneindeks returneres til "5".

Trinn # 3 - Nå vil kampformelen brukes i vlookup-funksjonen for å få verdien fra den identifiserte kolonneposisjonen.

Resultatet er vist nedenfor:

I formelen ovenfor plasseres samsvaringsfunksjonen i stedet for kolonneindeksparameteren for vlookup-funksjonen. Her vil samsvarsfunksjonen identifisere referansecellen "C8" for oppslagsverdien og returnere kolonnetallet gjennom den gitte tabellmatrisen. Denne kolonneposisjonen vil tjene formålet som input til kolonneindeksargumentet i vlookup-funksjonen. Hvilket vil igjen hjelpe vlookup med å identifisere verdien som skal returneres fra det resulterende kolonneindeksnummeret?

På samme måte har vi brukt vlookup med kampformel også for kolonnen "Drivstoff".

Resultatet er vist nedenfor:

Vi kan derved bruke denne kombinasjonsfunksjonen også for andre kolonner "Type" og "Fuel".

Ting å huske

  • VLOOKUP kan bare brukes på oppslagsverdier på den fremste venstre siden. Eventuelle verdier som skal søkes på høyre side av datatabellen vil returnere feilverdien “# N / A”.
  • Området for table_array som er angitt i det andre argumentet, skal være absolutt cellehenvisning "$", dette vil opprettholde det faste tabellmatrixområdet når du søker oppslagsformelen til andre celler, ellers vil referansecellene for tabellarrayområdet skifte til neste celle henvisning.
  • Verdien som er angitt i oppslagsverdien, skal ikke være mindre enn den minste verdien i den første kolonnen i tabelloppsettet, ellers returnerer funksjonen feilverdien “# N / A”.
  • Før du bruker en omtrentlig kamp “SANT” eller “1” i det siste argumentet, må du alltid huske å sortere tabelloppstillingen i stigende rekkefølge.
  • Kampfunksjonen returnerer bare verdien til verdien i vlookup-tabellmatrisen og returnerer ikke verdien.
  • I tilfelle Matchfunksjon ikke klarer å identifisere posisjonen til oppslagsverdien i tabelloppsettet, returnerer formelen "# N / A" i feilverdien.
  • Vlookup- og matchfunksjoner er ikke store og små bokstaver når de samsvarer med oppslagsverdien og den samsvarende tekstverdien i tabelloppstillingen.