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 , MATCH funktioner, der inkorporerer MMULT, TRANSPOSE , 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 et TRUE og FALSE array som dette:
{TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
Den dobbelte negative vil så konvertere TRUEs og FALSEs til 1'ere og 0'ere 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 på denne måde: {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ælpen 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
Kutools til Excel kan prale af over 300 funktioner, At sikre, at det, du har brug for, kun er et klik væk...
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.
- Giver effektive faner til Office (inkluderer Excel), ligesom Chrome, Edge og Firefox.
