Obecný vzorec
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
souhrn
Chcete-li vyhledat hodnotu porovnáním mezi více sloupci, můžete použít maticový vzorec založený na MMULT, TRANSPOSE, COLUMN a INDEX. V zobrazeném příkladu je vzorec v H4:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
kde „names“ je pojmenovaný rozsah C4: E7 a „groups“ je pojmenovaný rozsah B4: B7. Vzorec vrátí skupinu, do které každé jméno patří.
Poznámka: Toto je maticový vzorec a musí být zadán pomocí Enter Control Shift.
Vysvětlení
Při práci zevnitř ven je logickým kritériem použitým v tomto vzorci:
--(names=G4)
kde names je pojmenovaný rozsah C4: E7. To generuje výsledek TRUE / FALSE pro každou hodnotu v datech a dvojitý zápor vynutí hodnoty TRUE FALSE na 1 a 0 a získá tak pole:
(0,0,0;1,0,0;0,0,0;0,0,0)
Toto pole má 4 řádky a 3 sloupce, což odpovídá struktuře „jmen“.
S tímto výrazem se vytvoří druhé pole:
TRANSPOSE(COLUMN(names)^0))
Funkce COLUMN se používá k vytvoření číselného pole se 3 sloupci a 1 řádkem a funkce TRANSPOSE toto pole převede na 1 sloupec a 3 řádky. Zvyšování na sílu nula jednoduše převede všechna čísla v poli na 1. Funkce MMULT se poté použije k násobení matic:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
a výsledný jde do funkce MATCH jako pole, s 1 jako vyhledávací hodnotou:
MATCH(1,(0;1;0;0),0)
Funkce MATCH vrací pozici první shody, která odpovídá řádku první vyhovující kritéria splňující zadaný řádek. Toto je zdroj do INDEXU jako číslo řádku s pojmenovaným rozsahem „groups“ jako pole:
=INDEX(groups,2)
Nakonec INDEX vrátí „Bear“, do které patří Adam.
Doslovný obsahuje pro kritéria
Chcete-li namísto přesné shody zkontrolovat konkrétní textové hodnoty, můžete použít funkce ISNUMBER a SEARCH společně. Chcete-li například porovnat buňky obsahující slovo „jablko“, můžete použít:
=ISNUMBER(SEARCH("apple",data))
Tento vzorec je vysvětlen zde.
