Denne opplæringen lærer deg hvordan du bruker INDEX & MATCH -kombinasjonen til å utføre oppslag i Excel og Google Sheets.
INDEX & MATCH, The Perfect Pair
La oss se nærmere på noen av måtene du kan kombinere INDEX- og MATCH -funksjonene. MATCH -funksjonen er designet for å returnere den relative posisjonen til et element i en matrise, mens INDEX -funksjonen kan hente et element fra en matrise gitt en bestemt posisjon. Denne synergien mellom de to lar dem utføre nesten alle typer oppslag du måtte trenge.
INDEX / MATCH -kombinasjonen har historisk blitt brukt som en erstatning for VLOOKUP -funksjonen. En av hovedårsakene er muligheten til å lete opp til venstre (se neste avsnitt).
Merk: den nye XLOOKUP-funksjonen kan nå utføre venstreoppslag.
Slå opp til venstre
La oss bruke denne tabellen med basketballstatistikk:
Vi ønsker å finne Bob's Player #. Fordi Player # er til venstre for navnekolonnen, kan vi ikke bruke en VLOOKUP.
I stedet kunne vi gjøre en grunnleggende MATCH -forespørsel for å beregne Bobs rad
= MATCH (H2, B2: B5, 0)
Dette vil se etter en nøyaktig samsvar med ordet "Bob", og derfor vil vår funksjon returnere tallet 2, siden "Bob" er i 2nd posisjon.
Deretter kan vi bruke INDEX -funksjonen til å returnere Player #, tilsvarende en rad. For nå, la oss bare manuelt skrive inn "2" i funksjonen:
= INDEKS (A2: A5, 2)
Her vil INDEX referere til A3, siden det er 2nd celle innenfor A2: A5 -området og returnere resultatet av 42. For vårt overordnede mål kan vi deretter kombinere disse to til:
= INDEKS (A2: A5, MATCH (H2, B2: B5, 0))
Fordelen her er at vi var i stand til å returnere et resultat fra en kolonne til venstre for der vi søkte.
To-dimensjon oppslag
La oss se på bordet vårt fra før:
Denne gangen ønsker vi imidlertid å hente en spesifikk statistikk. Vi har kalt ut at vi vil søke etter Rebounds i celle H1. I stedet for å måtte skrive flere IF -setninger for å avgjøre hvilken kolonne du vil hente resultatet fra, kan du bruke en MATCH -funksjon igjen. INDEX -funksjonen lar deg spesifisere radverdien og kolonneverdien. Vi skal legge til en annen MATCH -funksjon her for å avgjøre hvilken kolonne vi vil ha. Det vil se ut som
= MATCH (H1, A1: E1, 0)
Cellen vår i H1 er en rullegardinmeny som lar oss velge hvilken kategori vi vil søke etter, og deretter bestemmer vår MATCH hvilken kolonne i tabellen som tilhører. La oss koble denne nye biten til vår forrige formel. Vær oppmerksom på at vi må justere det første argumentet for å være to dimensjoner, ettersom vi ikke lenger bare vil ha et resultat fra kolonne A.
= INDEKS (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
I vårt eksempel ønsker vi å finne Rebounds for Charlie. Formelen vår kommer til å evaluere dette slik:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Vi har nå opprettet et fleksibelt oppsett som lar brukeren hente hvilken verdi de vil fra tabellen vår uten å måtte skrive flere formler eller forgrenede IF -setninger.
Flere seksjoner
Den brukes ikke ofte, men INDEX har et femte argument som kan gis for å avgjøre hvilket område innenfor argument en å bruke. Dette betyr at vi trenger en måte å overføre flere områder til det første argumentet. Du kan gjøre dette ved å bruke et ekstra sett med parenteser. Dette eksemplet vil illustrere hvordan du kan hente resultater fra forskjellige tabeller i et regneark ved å bruke INDEX.
Her er oppsettet vi skal bruke. Vi har statistikk for tre forskjellige kvartaler.
I cellene H1: H3 har vi opprettet rullegardinlister for datavalidering for våre forskjellige valg. Nedtrekksmenyen for kvartalet kommer fra J2: J4. Vi bruker dette til en annen MATCH -setning, for å avgjøre hvilket område vi skal bruke. Formelen vår i H4 kommer til å se slik ut:
= INDEKS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Vi har allerede diskutert hvordan de to indre MATCH -funksjonene fungerer, så la oss fokusere på de første og siste argumentene:
= INDEKS ((A3: E6, A10: E13, A17: E20), …, MATCH (H3, J2: J4, 0))
Vi har gitt INDEX -funksjonen flere matriser i det første argumentet ved å legge dem alle i parentes. Den andre måten du kan gjøre dette på er ved å bruke formler - Definer navn. Du kan definere et navn kalt "MyTables" med en definisjon av
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
La oss gå tilbake til hele utsagnet. Våre forskjellige MATCH -funksjoner kommer til å fortelle INDEX -funksjonen nøyaktig hvor de skal lete. Først bestemmer vi at "Charlie" er 3rd rad. Deretter ønsker vi "Rebounds", som er 4th kolonne. Til slutt har vi bestemt at vi ønsker resultatet fra 2nd bord. Formelen vil vurdere gjennom dette slik:
= INDEKS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEKS ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEKS (A10: E13, 3, 4) = D13 = 14
Som vi nevnte i begynnelsen av dette eksemplet, er du begrenset til å ha tabellene i det samme regnearket. Hvis du kan skrive ut riktige måter å fortelle din INDEX hvilken rad, kolonne og/eller område du vil hente data fra, vil INDEX tjene deg veldig godt.
Google Regneark -INDEKS OG MATCH
Alle eksemplene ovenfor fungerer nøyaktig det samme i Google Regneark som i Excel.
