I denne artikel lærer vi, hvordan du bruger INDEX og MATCH -funktionen til at slå op i værdi i Excel.
INDEX & MATCH -funktion for at erstatte vlookup -funktion
Vi bruger normalt VLOOKUP -funktionen til at finde en værdi, der kender rækkeindekset og kolonnummeret. For VLOOKUP -funktionen skal vi have kolonnetal- og rækken til at matche kriterier og den mest afgørende ting værdien for at finde ud af at være til højre for matchende værdi. Men dataene understøtter ikke altid det. Mange gange skal vi bare slå værdier op uden at kende række- eller kolonneindekset, bare have de værdier, der skal matche. For eksempel: at finde løn til en medarbejder, der har medarbejder -id. I dette vil vi kigge efter Medarbejder -ID for rækkeindeks og Løn for kolonneindeks. Lad os lære, hvordan vi kan gøre dette opslag ved hjælp af INDEX & MATCH.
Hvordan løses problemet?
For at formlen først skal forstå, skal vi revidere lidt om følgende funktioner
- INDEX -funktion
- MATCH -funktion
Nu laver vi en formel ved hjælp af ovenstående funktioner. Match -funktionen returnerer indekset for opslagsværdien1 i rækkeoverskriftsfeltet. Og en anden MATCH -funktion returnerer indekset for opslagsværdien 2 fra kolonneoverskriftsfeltet. Indeksnumrene indføres nu i INDEX -funktionen for at få værdierne under opslagsværdien fra tabeldataene.
Generisk formel:
| = INDEX (data, MATCH (opslag_værdi1, rækkehoveder, 0, MATCH (opslag_værdi2, kolonneoverskrifter, 0))) |
data : array af værdier inde i tabellen uden overskrifter
opslagsværdi1 : værdi til opslag i row_header.
række_overskrifter : Rækkeindeks -array til opslag.
opslagsværdi1 : værdi til opslag i column_header.
column_headers : kolonne Indeks array til opslag.
Eksempel:
Ovenstående udsagn kan være komplicerede at forstå. Så lad os forstå dette ved at bruge formlen i et eksempel
Her har vi en liste over scoringer opnået af elever med deres emneliste. Vi skal finde scoren for en bestemt studerende (Gary) og et emne (samfundsfag) som vist i øjebliksbilledet herunder.
Elevværdien1 skal matche rækken Header header, og Subject value2 skal matche Column_header arrayet.
Brug formlen i J6 -cellen:
| = INDEX (tabel, MATCH (J5, række, 0, MATCH (J4, kolonne, 0))) |
Forklaring:
- MATCH -funktionen matcher Elev -værdien i J4 -cellen med rækkehovedsætningen og returnerer sin position 3 som et tal.
- MATCH -funktionen matcher emneværdien i J5 -cellen med kolonneoverskriftsarrayet og returnerer sin position 4 som et tal.
- INDEX -funktionen tager række- og kolonneindeksnummeret og kigger op i tabeldataene og returnerer den matchede værdi.
- Argumentet MATCH type er fastsat til 0. Da formlen vil udtrække det nøjagtige match.
Her er værdier til formlen angivet som cellereferencer og row_header, tabel og column_header givet som navngivne områder.
Som du kan se i ovenstående snapshot, fik vi den score, som en elev opnåede Gary i Emne Sociale Studier som 36. Og det beviser, at formlen fungerer fint, og for tvivl se nedenstående noter for at forstå.
Nu vil vi bruge den omtrentlige match med rækkeoverskrifter og kolonneoverskrifter som tal. Ca. match tager kun talværdierne, da det ikke kan bruges på tekstværdier
Her har vi en pris på værdier i henhold til produktets højde og bredde. Vi skal finde prisen for en specifik højde (34) og bredde (21) som vist i øjebliksbilledet herunder.
Højdeværdien1 skal matche rækken Rækkehoved og Breddeværdi2 skal matche kolonnehovedets matrix.
Brug formlen i K6 -cellen:
| = INDEX (data, MATCH (K4, Højde, 1, MATCH (K5, Bredde, 1))) |
Forklaring:
- MATCH -funktionen matcher værdien Højde i K4 -cellen med rækkehovedsætningen og returnerer sin position 3 som et tal.
- MATCH -funktionen matcher breddeværdien i K5 -cellen med kolonneoverskriftsarrayet og returnerer sin position 2 som et tal.
- INDEX -funktionen tager række- og kolonneindeksnummeret og kigger op i tabeldataene og returnerer den matchede værdi.
- Argumentet MATCH type er fastsat til 1. Da formlen udtrækker den omtrentlige matchning.
Her er værdier til formlen angivet som cellereferencer og row_header, data og column_header givet som navngivne områder som nævnt i snapshotet ovenfor.
Du kan også udføre nøjagtige opslag ved hjælp af INDEX og MATCH -funktionen i Excel. Lær mere om, hvordan du foretager sagfølsom opslag ved hjælp af INDEX & MATCH -funktionen i Excel. Du kan også slå op efter de delvise kampe ved hjælp af jokertegnene i Excel.
Som du kan se i ovenstående snapshot, fik vi prisen opnået i højden (34) & Bredde (21) som 53.10. Og det beviser, at formlen fungerer fint, og for tvivl se nedenstående noter for mere forståelse.
Bemærkninger:
- Funktionen returnerer #NA -fejlen, hvis opslagsarrayargumentet til MATCH -funktionen er 2 D -array, der er dataoverskriftsfeltet …
- Funktionen matcher den nøjagtige værdi, da matchtype -argumentet til MATCH -funktionen er 0.
- Opslagsværdierne kan angives som cellereference eller direkte ved hjælp af citatsymbol (") i formlen som argumenter.
Håber denne artikel om Sådan bruges Brug INDEX og MATCH -funktionen til at søge værdi i Excel er forklarende. Find flere artikler om beregning af værdier og relaterede Excel -formler her. Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet.
Brug INDEX og MATCH til at slå op på værdi : INDEX-MATCH-formlen bruges til at søge dynamisk og præcist en værdi i en given tabel. Dette er et alternativ til VLOOKUP -funktionen, og det overvinder manglerne i VLOOKUP -funktionen.
Brug VLOOKUP fra to eller flere opslagstabeller : For at slå op fra flere tabeller kan vi tage en IFERROR -tilgang. For at slå op fra flere tabeller tager det fejlen som en switch til den næste tabel. En anden metode kan være en If -tilgang.
Sådan laver du store og små bogstaver i Excel : Excels VLOOKUP -funktion er ikke store og små bogstaver, og den returnerer den første matchede værdi fra listen. INDEX-MATCH er ingen undtagelse, men det kan ændres for at gøre det store og små bogstaver. Lad os se, hvordan…
Opslag, der ofte vises tekst med kriterier i Excel : Opslaget vises oftest i tekst i et område, vi bruger INDEX-MATCH med MODE-funktionen. Her er metoden.
Populære artikler:
Sådan bruges IF -funktionen i Excel : IF -sætningen i Excel kontrollerer betingelsen og returnerer en bestemt værdi, hvis betingelsen er SAND, eller returnerer en anden specifik værdi, hvis FALSK.
Sådan bruges VLOOKUP -funktionen i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
Sådan bruges COUNTIF -funktionen i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.