Bruk INDEX og MATCH -funksjonen til å slå opp verdi i Excel

Anonim

I denne artikkelen lærer vi hvordan du bruker INDEX og MATCH -funksjonen til å slå opp verdi i Excel.

INDEX & MATCH -funksjon for å erstatte vlookup -funksjonen

Vi bruker vanligvis VLOOKUP -funksjonen for å finne en verdi som kjenner radindeksen og kolonnummeret. For VLOOKUP -funksjonen må vi ha kolonnetall og radmatchingskriterier og det viktigste av verdien for å finne ut å være til høyre for matchende verdi. Men dataene støtter ikke alltid det. Mange ganger må vi bare slå opp verdier uten å kjenne rad- eller kolonneindeksen, bare ha verdiene som skal matche. For eksempel: finne lønnen til en ansatt som har ansatt -ID. I dette vil vi se etter medarbeider -ID for radindeks og lønn for kolonneindeks. La oss lære hvordan vi kan gjøre dette oppslaget ved å bruke INDEX & MATCH.

Hvordan løse problemet?

For at formelen først skal forstå må vi revidere litt om følgende funksjoner

  1. INDEX -funksjon
  2. MATCH -funksjon

Nå skal vi lage en formel ved å bruke funksjonene ovenfor. Match -funksjonen returnerer indeksen for oppslagsverdien1 i radoverskriftsfeltet. Og en annen MATCH -funksjon vil returnere indeksen for oppslagsverdien 2 fra kolonneoverskriftsfeltet. Indeksnumrene vil nå bli matet inn i INDEX -funksjonen for å få verdiene under oppslagsverdien fra tabelldataene.

Generisk formel:

= INDEX (data, MATCH (oppslag_verdi1, rad_overskrifter, 0, MATCH (oppslag_verdi2, kolonneoverskrifter, 0)))

data : rekke verdier inne i tabellen uten overskrifter

oppslagsverdi1 : verdi for oppslag i rad_hodet.

row_headers : Radindeksmatrise for oppslag.

oppslagsverdi1 : verdi for oppslag i column_header.

column_headers : kolonne Indeksmatrise for oppslag.

Eksempel:

Uttalelsene ovenfor kan være kompliserte å forstå. Så la oss forstå dette ved å bruke formelen i et eksempel

Her har vi en liste over poengsummer oppnådd av studenter med emnelisten. Vi må finne poengsummen for en bestemt student (Gary) og emne (samfunnsfag) som vist i øyeblikksbildet nedenfor.

Studentverdien1 må samsvare med Row_header -arrayet og Subject value2 må matche Column_header -arrayet.

Bruk formelen i J6 -cellen:

= INDEKS (tabell, MATCH (J5, rad, 0, MATCH (J4, kolonne, 0)))

Forklaring:

  • MATCH -funksjonen matcher studentverdien i J4 -cellen med radoverskriftsarrayen og returnerer posisjonen 3 som et tall.
  • MATCH -funksjonen samsvarer med Subject -verdien i J5 -cellen med kolonneoverskriften og returnerer posisjonen 4 som et tall.
  • INDEKS -funksjonen tar rad- og kolonneindeksnummeret og ser opp i tabelldataene og returnerer den samsvarende verdien.
  • MATCH -type -argumentet er fikset til 0. Siden formelen trekker ut eksakt samsvar.

Her er verdier til formelen gitt som cellereferanser og row_header, tabell og column_header gitt som navngitte områder.

Som du kan se i øyeblikksbildet ovenfor, fikk vi poengsummen oppnådd av studenten Gary i Emne Sosiale studier som 36. Og det viser at formelen fungerer bra, og for tvil, se merknadene nedenfor for å forstå.

Nå vil vi bruke den omtrentlige kampen med radoverskrifter og kolonneoverskrifter som tall. Omtrentlig samsvar tar bare tallverdiene, ettersom det ikke er mulig på tekstverdier

Her har vi en pris på verdier i henhold til produktets høyde og bredde. Vi må finne prisen for en bestemt høyde (34) og bredde (21) som vist i øyeblikksbildet nedenfor.

Høydeverdi1 må samsvare med radhodet -matrisen og breddeverdi2 må samsvare med kolonnehodet -matrisen.

Bruk formelen i K6 -cellen:

= INDEKS (data, MATCH (K4, Høyde, 1, MATCH (K5, Bredde, 1)))

Forklaring:

  • MATCH -funksjonen matcher høydeverdien i K4 -cellen med radoverskriftsarrayen og returnerer posisjonen 3 som et tall.
  • MATCH -funksjonen matcher breddeverdien i K5 -cellen med kolonneoverskriftsarrayen og returnerer posisjonen 2 som et tall.
  • INDEKS -funksjonen tar rad- og kolonneindeksnummeret og ser opp i tabelldataene og returnerer den samsvarende verdien.
  • MATCH -type -argumentet er fikset til 1. Siden formelen trekker ut omtrentlig samsvar.

Her er verdier til formelen gitt som cellereferanser og row_header, data og column_header gitt som navngitte områder som nevnt i øyeblikksbildet ovenfor.

Du kan også utføre nøyaktige oppslag ved å bruke INDEX og MATCH -funksjonen i Excel. Lær mer om hvordan du gjør saksfølsom oppslag ved hjelp av INDEX & MATCH -funksjonen i Excel. Du kan også slå opp for de delvise treffene ved hjelp av jokertegnene i Excel.

Som du kan se i øyeblikksbildet ovenfor, fikk vi prisen oppnådd etter høyde (34) & Bredde (21) som 53,10. Og det viser at formelen fungerer bra, og for tvil, se merknadene nedenfor for mer forståelse.

Merknader:

  1. Funksjonen returnerer #NA -feilen hvis oppslagsarrayargumentet til MATCH -funksjonen er 2 D -array som er topptekstfeltet til dataene …
  2. Funksjonen matcher den eksakte verdien ettersom matchningstypeargumentet til MATCH -funksjonen er 0.
  3. Oppslagsverdiene kan gis som cellereferanse eller direkte ved hjelp av sitatsymbol (") i formelen som argumenter.

Håper denne artikkelen om hvordan du bruker Bruk INDEX og MATCH -funksjonen til å slå opp verdi i Excel er forklarende. Finn flere artikler om beregning av verdier og relaterte Excel -formler her. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post.

Bruk INDEX og MATCH til å slå opp verdi : INDEX-MATCH-formelen brukes til å slå dynamisk og presist opp en verdi i en gitt tabell. Dette er et alternativ til VLOOKUP -funksjonen, og den overvinner manglene i VLOOKUP -funksjonen.

Bruk VLOOKUP fra to eller flere oppslagstabeller : For å slå opp fra flere tabeller kan vi ta en IFERROR -tilnærming. For å slå opp fra flere tabeller, tar det feilen som en bryter for den neste tabellen. En annen metode kan være en If -tilnærming.

Hvordan gjøre saksfølsom oppslag i Excel : Excels VLOOKUP -funksjon er ikke store og små bokstaver, og den returnerer den første matchede verdien fra listen. INDEX-MATCH er intet unntak, men det kan endres for å gjøre det saksfølsomt. La oss se hvordan …

Oppslag som ofte vises tekst med kriterier i Excel : Søket vises oftest i tekst i et område vi bruker INDEX-MATCH with MODE-funksjonen. Her er metoden.

Populære artikler:

Slik bruker du IF -funksjonen i Excel : IF -setningen i Excel sjekker tilstanden og returnerer en bestemt verdi hvis betingelsen er SANN eller returnerer en annen spesifikk verdi hvis FALSE.

Slik bruker du VLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.

Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.

Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.