INDEX og MATCH på tværs af flere kolonner
For at slå en værdi op ved at matche på tværs af flere kolonner skal du bruge en matrixformel baseret på INDEX og MATCH funktioner, der inkorporerer MMULT, TRANSPOSE og KOLONNE vil gøre dig en tjeneste.
Hvordan slår man en værdi op ved at matche på tværs af flere kolonner?
For at udfylde tilsvarende klasse for hver elev som ovenstående tabel vist, hvor oplysningerne er opført på tværs af flere kolonner, kan du først bruge tricket med funktionen MMULT, TRANSPOSE og COLUMN til at producere en matrix-array. Så vil MATCH-funktionen give dig positionen for din opslagsværdi, som vil blive ført til INDEX for at hente den værdi, du leder efter i arrayet.
Generisk syntaks
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Bemærk: Dette er en matrixformel, der kræver, at du indtaster med Ctrl + Flytte + Indtast.
- returområde: Det område, hvor du vil have formlen til at returnere klasseoplysningerne fra. Her refererer til klasserækken.
- opslagsværdi: Værdien formlen brugte til at finde dens tilsvarende klasseinformation. Her refererer til fornavnet.
- opslag_array: Celleområdet, hvor opslagsværdi er opført; Området med de værdier, der skal sammenlignes med opslagsværdi. Her refererer til navneområdet.
- match_type 0: Tvinger MATCH til at finde den første værdi, der nøjagtigt er lig med opslagsværdi.
For at finde klasse af Jimmy, kopier eller indtast formlen herunder i cellen H5, og tryk på Ctrl + Flytte + Indtast for at få resultatet:
= INDEX ($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(KOLONNE($C$5:$E$7)^0)),0)))
√ Bemærk: Dollartegnene ($) ovenfor angiver absolutte referencer, hvilket betyder, at navn og klasseområder i formlen ikke ændres, når du flytter eller kopierer formlen til andre celler. Bemærk, at du ikke bør tilføje dollartegn til den cellereference, der repræsenterer opslagsværdien, da du ønsker, at den skal være relativ, når du kopierer den til andre celler. Når du har indtastet formlen, skal du trække udfyldningshåndtaget ned for at anvende formlen på nedenstående celler.
Forklaring af formlen
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- --($C$5:$E$7=G5): Dette segment kontrollerer hver værdi i området $C$5:$E$7 hvis de er lig med værdien i cellen G5, og genererer en TRUE og FALSE matrix som denne:
{TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
Den dobbelte negative vil så konvertere TRUEs og FALSEs til 1'er og 0'er for at give et array som dette:
{1,0,0; 0,0,0; 0,0,0}. - KOLONNE($C$5:$E$7): Funktionen KOLONNE returnerer kolonnenumrene for området $C$5:$E$7 i et array som dette: 3,4,5 {}.
- TRANSPOSER(KOLONNE($C$5:$E$7)^0) = TRANSPOSER(3,4,5 {}^0): Efter at have hævet potensen til 0, vil alle tallene i arrayet {3,4,5} blive konverteret til 1: {1,1,1}. TRANSPOSE-funktionen konverterer derefter kolonne-arrayet til en række-array som dette: {1; 1; 1}.
- MMULT(--($C$5:$E$7=G5),TRANSPOSER(KOLONNE($C$5:$E$7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): MMULT-funktionen returnerer matrixproduktet af de to arrays sådan her: {1; 0; 0}.
- MATCH (1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(KOLONNE($C$5:$E$7)^0)), 0) = MATCH (1,{1; 0; 0}, 0): Match_type 0 tvinger MATCH-funktionen til at returnere positionen for den første match af 1 i arrayet {1; 0; 0}, Hvilket er 1.
- INDEKS($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(KOLONNE($C$5:$E$7)^0)), 0))) = INDEX ($ B $ 5: $ B $ 7,1): INDEX -funktionen returnerer 1st værdi i klasseområdet $ B $ 5: $ B $ 7, Hvilket er A.
For nemt at finde en værdi ved at matche på tværs af flere kolonner, kan du også bruge vores professionelle Excel-tilføjelsesprogram Kutools til Excel. Se instruktionen her for at udføre missionen.
Relaterede funktioner
Excel INDEX -funktionen returnerer den viste værdi baseret på en given position fra et område eller en matrix.
Funktionen Excel MATCH søger efter en bestemt værdi i et celleområde og returnerer værdiens relative position.
Excel MMULT-funktionen returnerer matrixproduktet af to arrays. Matrixresultatet har det samme antal rækker som matrix1 og det samme antal kolonner som matrix2.
Excel TRANSPOSE-funktionen roterer orienteringen af et område eller en matrix. For eksempel kan den rotere en tabel, der er arrangeret vandret i rækker til lodret i kolonner eller omvendt.
Funktionen KOLONNE returnerer antallet af kolonner, som formlen vises, eller returnerer kolonnenummeret for en given reference. For eksempel returnerer formel =KOLONNE(BD) 56.
Relaterede formler
Opslag med flere kriterier med INDEX og MATCH
Når man behandler en stor database i et Excel -regneark med flere kolonner og rækketekster, er det altid svært at finde noget, der opfylder flere kriterier. I dette tilfælde kan du bruge en matrixformel med INDEX- og MATCH -funktionerne.
To-vejs opslag med INDEX og MATCH
For at søge efter noget på tværs af både rækker og kolonner i Excel, eller vi siger at slå en værdi op i skæringspunktet mellem den specifikke række og kolonne, kan vi bruge hjælp fra INDEX og MATCH -funktioner.
Opslag nærmeste matchværdi med flere kriterier
I nogle tilfælde skal du muligvis søge den nærmeste eller omtrentlige matchværdi baseret på mere end ét kriterium. Med kombinationen af INDEX-, MATCH- og IF -funktioner kan du hurtigt få det gjort i Excel.
De bedste Office-produktivitetsværktøjer
Kutools til Excel - Hjælper dig med at skille dig ud fra mængden
Kunne du tænke dig at udføre dit daglige arbejde hurtigt og perfekt? Kutools til Excel bringer 300 kraftfulde avancerede funktioner (Kombiner projektmapper, sum efter farve, delt celleindhold, konverter dato, og så videre...) og spar 80% tid for dig.
- Designet til 1500 arbejdsscenarier, hjælper dig med at løse 80 % Excel-problemer.
- Reducer tusindvis af tastatur- og museklik hver dag, lindre dine trætte øjne og hænder.
- Bliv en Excel-ekspert på 3 minutter. Behøver ikke længere huske smertefulde formler og VBA-koder.
- 30-dages ubegrænset gratis prøveperiode. 60-dages pengene tilbage garanti. Gratis opgradering og support i 2 år.
Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)
- Et sekund til at skifte mellem snesevis af åbne dokumenter!
- Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
- Øger din produktivitet med 50 %, når du ser og redigerer flere dokumenter.
- Bringer effektive faner til Office (inkluderer Excel), Ligesom Chrome, Firefox og ny Internet Explorer.
