INDEX MATCH

Denne vejledning lærer dig, hvordan du bruger kombinationen INDEX & MATCH til at udføre opslag i Excel og Google Sheets.

INDEX & MATCH, Det perfekte par

Lad os se nærmere på nogle af de måder, du kan kombinere INDEX- og MATCH -funktionerne på. MATCH -funktionen er designet til at returnere den relative position af et element i et array, mens INDEX -funktionen kan hente et element fra et array, der får en bestemt position. Denne synergi mellem de to giver dem mulighed for at udføre næsten enhver form for opslag, du måske har brug for.

INDEX / MATCH -kombinationen har historisk været brugt som en erstatning for VLOOKUP -funktionen. En af de primære årsager er muligheden for at foretage et opslag til venstre (se næste afsnit).

Bemærk: den nye XLOOKUP-funktion kan nu udføre opslag, der ser ud til venstre.

Slå op til venstre

Lad os bruge denne tabel med basketballstatistik:

Vi vil finde Bob's Player #. Fordi Player # er til venstre for navnekolonnen, kan vi ikke bruge en VLOOKUP.

I stedet kunne vi lave en grundlæggende MATCH -anmodning om at beregne Bobs række

= MATCH (H2, B2: B5, 0)

Dette vil lede efter en nøjagtig match af ordet "Bob", og derfor ville vores funktion returnere tallet 2, da "Bob" er i 2^nd position.

Dernæst kan vi bruge INDEX -funktionen til at returnere Player #, svarende til en række. For nu skal vi bare manuelt indtaste "2" i funktionen:

= INDEKS (A2: A5, 2)

Her vil INDEX referere til A3, da det er 2^nd celle inden for A2: A5 -området og returnere resultatet af 42. For vores overordnede mål kan vi derefter kombinere disse to til:

= INDEX (A2: A5, MATCH (H2, B2: B5, 0))

Fordelen her er, at vi kunne returnere et resultat fra en kolonne til venstre for, hvor vi søgte.

To-dimensionel opslag

Lad os se på vores bord fra før:

Denne gang ønsker vi imidlertid at hente en specifik statistik. Vi har kaldt ud, at vi vil søge efter Rebounds i celle H1. I stedet for at skulle skrive flere IF -sætninger for at afgøre, hvilken kolonne resultatet skal hentes fra, kan du bruge en MATCH -funktion igen. INDEX -funktionen lader dig angive rækkeværdien og kolonneværdien. Vi vil tilføje en anden MATCH -funktion her for at bestemme, hvilken kolonne vi ønsker. Det vil se ud

= MATCH (H1, A1: E1, 0)

Vores celle i H1 er en rullemenu, der lader os vælge, hvilken kategori vi vil søge efter, og derefter bestemmer vores MATCH hvilken kolonne i tabellen, der tilhører. Lad os tilslutte denne nye bit til vores tidligere formel. Bemærk, at vi skal justere det første argument til at være to dimensioner, da vi ikke længere bare vil have et resultat fra kolonne A.

= INDEX (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))

I vores eksempel vil vi finde Rebounds til Charlie. Vores formel vil evaluere dette sådan:

= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6

Vi har nu oprettet en fleksibel opsætning, der giver brugeren mulighed for at hente enhver værdi, de ønsker, fra vores tabel uden at skulle skrive flere formler eller forgrenede IF -sætninger.

Flere sektioner

Det bruges ikke ofte, men INDEX har et femte argument, der kan gives for at bestemme hvilket areal inden for argument en at bruge. Det betyder, at vi har brug for en måde at videregive flere områder til det første argument. Du kan gøre dette ved at bruge et ekstra sæt parenteser. Dette eksempel illustrerer, hvordan du kunne hente resultater fra forskellige tabeller på et regneark ved hjælp af INDEX.

Her er det layout, vi vil bruge. Vi har statistik for tre forskellige fjerdedele af spillet.

I celler H1: H3 har vi oprettet rullelister med datavalidering til vores forskellige valg. Rullemenuen til kvartalet kommer fra J2: J4. Vi bruger dette til en anden MATCH -erklæring for at bestemme hvilket område der skal bruges. Vores formel i H4 kommer til at se sådan ud:

= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))

Vi har allerede diskuteret, hvordan de to indre MATCH -funktioner fungerer, så lad os fokusere på de første og sidste argumenter:

= INDEX ((A3: E6, A10: E13, A17: E20), …, MATCH (H3, J2: J4, 0))

Vi har givet INDEX -funktionen flere arrays i det første argument ved at omslutte dem alle i parentes. Den anden måde du kan gøre dette på er ved at bruge formler - Definer navn. Du kan definere et navn kaldet "MyTables" med en definition af

= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))

Lad os gå tilbage til hele udsagnet. Vores forskellige MATCH -funktioner fortæller INDEX -funktionen præcis, hvor de skal kigge. Først bestemmer vi, at "Charlie" er 3^rd række. Dernæst vil vi have "Rebounds", som er 4^th kolonne. Endelig har vi fastslået, at vi ønsker resultatet fra 2^nd bord. Formlen vil evaluere gennem dette sådan:

= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEX ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEX (A10: E13, 3, 4) = D13 = 14

Som vi nævnte i begyndelsen af ​​dette eksempel, er du begrænset til at have tabellerne på det samme regneark. Hvis du kan skrive korrekte måder at fortælle din INDEX, hvilken række, kolonne og/eller område du vil hente data fra, vil INDEX tjene dig meget godt.

Google Sheets -INDEX & MATCH

Alle ovenstående eksempler fungerer nøjagtig det samme i Google Sheets som i Excel.