Generisk formel
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Sammendrag
For å slå opp en verdi ved å matche på tvers av flere kolonner, kan du bruke en matriseformel basert på MMULT, TRANSPOSE, COLUMN og INDEX. I eksemplet vist er formelen i H4:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
der "navn" er det navngitte området C4: E7, og "grupper" er det navngitte området B4: B7. Formelen returnerer gruppen som hvert navn tilhører.
Merk: dette er en matriseformel og må angis med kontrollskift.
Forklaring
Arbeidet fra innsiden og ut, de logiske kriteriene som brukes i denne formelen er:
--(names=G4)
hvor navn er det navngitte området C4: E7. Dette genererer et SANT / FALSE-resultat for hver verdi i data, og det dobbelte negative tvinger de SANNE FALSE-verdiene til 1 og 0 for å gi en matrise som dette:
(0,0,0;1,0,0;0,0,0;0,0,0)
Denne matrisen er 4 rader med 3 kolonner, som samsvarer med strukturen til "navn".
En annen matrise opprettes med dette uttrykket:
TRANSPOSE(COLUMN(names)^0))
COLUMN-funksjonen brukes til å lage en numerisk matrise med 3 kolonner og 1 rad, og TRANSPOSE konverterer denne matrisen til 1 kolonne og 3 rader. Å heve til kraften null konverterer ganske enkelt alle tall i matrisen til 1. MMULT-funksjonen brukes deretter til å utføre matrisemultiplikasjon:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
og det resulterende går inn i MATCH-funksjonen som matrise, med 1 som oppslagsverdi:
MATCH(1,(0;1;0;0),0)
MATCH-funksjonen returnerer posisjonen til den første kampen, som tilsvarer raden i den første matchende raden som oppfyller kriteriene. Dette mates inn i INDEX som radnummer, med det navngitte området "grupper" som matrise:
=INDEX(groups,2)
Til slutt returnerer INDEX "Bear", gruppen Adam tilhører.
Bokstavelig inneholder for kriterier
For å se etter spesifikke tekstverdier i stedet for en eksakt samsvar, kan du bruke funksjonene ISNUMBER og SØK sammen. For eksempel, for å matche celler som inneholder "apple", kan du bruke:
=ISNUMBER(SEARCH("apple",data))
Denne formelen er forklart her.
