Генеричка формула
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Резиме
Да бисте пронашли вредност подударањем у више колона, можете користити формулу низа засновану на ММУЛТ, ТРАНСПОСЕ, ЦОЛУМН и ИНДЕКС. У приказаном примеру, формула у Х4 је:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
где је „имена“ именовани опсег Ц4: Е7, а „групе“ именовани опсег Б4: Б7. Формула враћа групу којој припада свако име.
Напомена: ово је формула низа и мора се унети контролним помаком ентер.
Објашњење
Логични критеријуми који се користе у овој формули су:
--(names=G4)
где је имена именовани опсег Ц4: Е7. Ово генерише ТРУЕ / ФАЛСЕ резултат за сваку вредност у подацима, а двоструки негатив присиљава ТРУЕ ФАЛСЕ вредности на 1 и 0 дајући овакав низ:
(0,0,0;1,0,0;0,0,0;0,0,0)
Овај низ је 4 реда по 3 колоне, што се подудара са структуром „имена“.
Други низ се креира са овим изразом:
TRANSPOSE(COLUMN(names)^0))
Функција ЦОЛУМН се користи за стварање нумеричког низа са 3 колоне и 1 редом, а ТРАНСПОСЕ претвара овај низ у 1 колону и 3 реда. Подизање у нулу једноставно претвара све бројеве у низу у 1. Функција ММУЛТ се затим користи за множење матрице:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
и резултујући прелази у функцију МАТЦХ као низ, са 1 као вредност претраживања:
MATCH(1,(0;1;0;0),0)
Функција МАТЦХ враћа положај првог подударања, што одговара реду првог подударног реда који испуњава испоручене критеријуме. Ово се уводи у ИНДЕКС као број реда, са именованим опсегом "групе" као низом:
=INDEX(groups,2)
Коначно, ИНДЕКС враћа „Медвед“, групи којој Адам припада.
Дословно садржи критеријуме
Да бисте проверили да ли постоје одређене текстуалне вредности уместо тачног подударања, можете заједно да користите функције ИСНУМБЕР и СЕАРЦХ. На пример, за подударање ћелија које садрже „јабуку“ можете да користите:
=ISNUMBER(SEARCH("apple",data))
Ова формула је овде објашњена.
