Generisk formel
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Resumé
For at slå en værdi op ved at matche på tværs af flere kolonner kan du bruge en matrixformel baseret på MMULT, TRANSPOSE, COLUMN og INDEX. I det viste eksempel er formlen i H4:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
hvor "navne" er det navngivne område C4: E7, og "grupper" er det navngivne område B4: B7. Formlen returnerer den gruppe, som hvert navn tilhører.
Bemærk: dette er en matrixformel og skal indtastes med kontrolskiftindtastning.
Forklaring
Når man arbejder indefra og ud, er de logiske kriterier, der anvendes i denne formel:
--(names=G4)
hvor navne er det navngivne område C4: E7. Dette genererer et TRUE / FALSE-resultat for hver værdi i data, og det dobbelte negative tvinger de TRUE FALSE-værdier til 1 og 0 for at give en matrix som denne:
(0,0,0;1,0,0;0,0,0;0,0,0)
Denne matrix er 4 rækker med 3 kolonner, der matcher strukturen af "navne".
Et andet array oprettes med dette udtryk:
TRANSPOSE(COLUMN(names)^0))
COLUMN-funktionen bruges til at oprette et numerisk array med 3 kolonner og 1 række, og TRANSPOSE konverterer dette array til 1 kolonne og 3 rækker. Hævelse til nulstyrken konverterer simpelthen alle tal i arrayet til 1. MMULT-funktionen bruges derefter til at udføre matrixmultiplikation:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
og det resulterende går ind i MATCH-funktionen som array, med 1 som opslagsværdi:
MATCH(1,(0;1;0;0),0)
MATCH-funktionen returnerer positionen for den første kamp, der svarer til rækken i den første matchende række, der leverer kriterier. Dette tilføres INDEX som række nummer, med det navngivne interval "grupper" som array:
=INDEX(groups,2)
Endelig returnerer INDEX "Bear", gruppen Adam tilhører.
Bogstaveligt indeholder for kriterier
For at kontrollere specifikke tekstværdier i stedet for et nøjagtigt match kan du bruge funktionerne ISNUMBER og SEARCH sammen. For eksempel, for at matche celler, der indeholder "æble", kan du bruge:
=ISNUMBER(SEARCH("apple",data))
Denne formel forklares her.
