I denne artikkelen vil vi lære å slå opp verdier i 2d-tabellen ved hjelp av en INDEX-MATCH-MATCH-funksjon i Excel.
Scenario:
Anta at du må gjøre flere oppslag fra en tabell som har hundrevis av kolonner. I slike tilfeller vil det ta for lang tid å bruke forskjellige formler for hvert oppslag. Hva med å lage en dynamisk oppslagsformel som du kan slå opp ved hjelp av overskriften. Ja, vi kan gjøre dette. Denne formelen kalles INDEX MATCH MATCH formel, eller si en 2d oppslagsformel.
Hvordan løse problemet?
For at formelen først skal forstå må vi revidere litt om følgende funksjoner
- INDEX -funksjon
- MATCH -funksjon
INDEX -funksjonen returnerer verdien til en gitt indeks i en matrise.
MATCH -funksjonen returnerer indeksen for det første utseendet på verdien i en matrise (enkeltdimensjonsmatrise).
Nå skal vi lage en formel ved å bruke funksjonene ovenfor. Match -funksjonen returnerer indeksen for oppslagsverdien1 i radoverskriftsfeltet. Og en annen MATCH -funksjon returnerer indeksen for oppslagsverdien2 i kolonneoverskriftsfeltet. Indeksnummerene vil nå bli matet inn i INDEX -funksjonen for å få verdiene under oppslagsverdien fra 2D -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 å slå opp.
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.
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, da det ikke er mulig for 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.
Som du kan se i øyeblikksbildet ovenfor, har vi prisen oppnådd etter høyde (34) & Bredde (21) som 53,10. Det viser at formelen fungerer bra, og for tvil, se merknadene nedenfor for mer forståelse.
Merknader:
- Funksjonen returnerer #NA -feilen hvis oppslagsarrayargumentet til MATCH -funksjonen er 2 D -array som er topptekstfeltet til dataene …
- Funksjonen matcher den eksakte verdien ettersom matchningstypeargumentet til MATCH -funksjonen er 0.
- Oppslagsverdiene kan gis som cellereferanse eller direkte ved hjelp av sitatsymbol (") i formelen som argumenter.
Håper du forsto hvordan du bruker Lookup in 2 D -tabellen ved hjelp av INDEX & MATCH -funksjonen i Excel. Utforsk flere artikler i Excel -oppslagsverdi her. Skriv gjerne dine spørsmål nedenfor i kommentarfeltet. Vi vil sikkert hjelpe deg.
Bruk INDEX og MATCH til å slå opp verdi : INDEX & MATCH -funksjon for å slå opp verdien etter behov.
SUM -område med INDEX i Excel : Bruk INDEX -funksjonen for å finne SUMMEN av verdiene etter behov.
Slik bruker du SUM -funksjonen i Excel : Finn SUMMEN av tall ved hjelp av SUM -funksjonen forklart med eksempel.
Slik bruker du INDEX -funksjonen i Excel : Finn INDEX for matrisen ved hjelp av INDEX -funksjonen forklart med eksempel.
Slik bruker du MATCH -funksjonen i Excel : Finn MATCH i matrisen ved å bruke INDEX -verdien inne i MATCH -funksjonen forklart med eksempel.
Hvordan bruke LOOKUP -funksjonen i Excel : Finn oppslagsverdien i matrisen ved å bruke LOOKUP -funksjonen forklart med eksempel.
Slik bruker du VLOOKUP -funksjonen i Excel : Finn oppslagsverdien i matrisen ved å bruke VLOOKUP -funksjonen forklart med eksempel.
Slik bruker du HLOOKUP -funksjonen i Excel : Finn oppslagsverdien i matrisen ved å bruke HLOOKUP -funksjonen forklart med eksempel.
Populære artikler
50 Excel -snarvei for å øke produktiviteten
Rediger en rullegardinliste
Absolutt referanse i Excel
Hvis det er betinget formatering
Hvis det er jokertegn
Vlookup etter dato
Bli med for- og etternavn i excel
