Når skal du bruke Index-Match i stedet for VLOOKUP i Excel

2019

For de av dere som er velbevandret i Excel, er du mest sannsynlig veldig kjent med VLOOKUP- funksjonen. VLOOKUP- funksjonen brukes til å finne en verdi i en annen celle basert på noen samsvarende tekst i samme rad.

Hvis du fortsatt er ny til VLOOKUP- funksjonen, kan du sjekke ut mitt forrige innlegg på hvordan du bruker VLOOKUP i Excel.

Så kraftig som det har VLOOKUP en begrensning på hvordan den samsvarende referansetabellen må struktureres for at formelen skal fungere.

Denne artikkelen vil vise deg begrensningen der VLOOKUP ikke kan brukes og introdusere en annen funksjon i Excel som heter INDEX-MATCH som kan løse problemet.

INDEX MATCH Excel Eksempel

Ved hjelp av følgende eksempel Excel-regneark, har vi en liste over navnene på bileier og bilnavnet. I dette eksemplet vil vi prøve å ta tak i bil-ID basert på bilmodellen som er oppført under flere eiere, som vist nedenfor:

På et eget ark kalt CarType har vi en enkel bildatabase med ID, bilmodell og farge .

Med denne tabelloppsettet kan VLOOKUP- funksjonen bare fungere hvis dataene vi ønsker å hente er plassert i kolonnen til høyre for det vi prøver å matche ( bilmodellfelt ).

Med andre ord, med denne tabellstrukturen, siden vi prøver å matche den basert på bilmodellen, er den eneste informasjonen vi kan få, Farge (Ikke ID da ID- kolonnen er plassert til venstre for bilmodellkolonnen.)

Dette skyldes at med VLOOKUP må oppslagsverdien vises i den første kolonnen og oppslagskolonnene må være til høyre. Ingen av disse betingelsene er oppfylt i vårt eksempel.

Den gode nyheten er at INDEX-MATCH vil kunne hjelpe oss med å oppnå dette. I praksis kombinerer dette faktisk to Excel-funksjoner som kan fungere individuelt: INDEX- funksjon og MATCH- funksjon.

Men for formålet med denne artikkelen vil vi bare snakke om kombinasjonen av de to med det formål å replikere funksjonen til VLOOKUP .

Formelen kan virke å være litt lang og skremmende i begynnelsen. Men når du har brukt det flere ganger, lærer du syntaksen av hjertet.

Dette er den fulle formelen i vårt eksempel:

 = INDEKS (CarType $ A $ 2:! $ A $ 5, MATCH (B4, CarType $ B $ 2: $ B $ 5, 0))

Her er nedbrytingen for hver seksjon

= INDEX ( - "=" angir begynnelsen på formel i cellen og INDEX er den første delen av Excel-funksjonen som vi bruker.

CarType! $ A $ 2: $ A $ 5 - kolonnene på arket CarType der dataene vi ønsker å hente, finnes. I dette eksemplet, ID for hver bilmodell.

MATCH ( - Den andre delen av Excel-funksjonen som vi bruker.

B4 - cellen som inneholder søketekst som vi bruker ( bilmodell ) .

CarType! $ B $ 2: $ B $ 5 - Kolonnene på arket CarType med dataene som vi skal bruke for å matche søketeksten.

0)) - For å indikere at søketeksten må nøyaktig matche med teksten i den tilsvarende kolonnen (dvs. CarType! $ B $ 2: $ B $ 5 ). Hvis den eksakte kampen ikke er funnet, returnerer formelen # N / A.

Merk : Husk den dobbelte lukkebraketten på slutten av denne funksjonen "))" og kommaene mellom argumentene.

Personlig har jeg flyttet fra VLOOKUP og nå bruker INDEX-MATCH som det er i stand til å gjøre mer enn VLOOKUP.

INDEX-MATCH- funksjonene har også andre fordeler sammenlignet med VLOOKUP :

Raskere beregninger

Når vi jobber med store datasett hvor beregningen selv kan ta lang tid på grunn av mange VLOOKUP-funksjoner, vil du oppdage at når du erstatter alle disse formlene med INDEX-MATCH, beregnes den totale beregningen raskere.

Du trenger ikke å telle relative kolonner

Hvis vår referansetabell har nøkkelteksten vi vil søke i kolonne C, og dataene vi trenger å få er i kolonne AQ, må vi vite / telle hvor mange kolonner som er mellom kolonne C og kolonne AQ ved bruk av VLOOKUP .

Med INDEX-MATCH-funksjonene kan vi direkte velge indekskolonnen (dvs. kolonne AQ) der vi trenger å få dataene og velg kolonnen som skal matches (dvs. kolonne C).