Лесно е да търсите стойност с един уникален ключ в таблица. Можем просто да използваме функцията VLOOKUP. Но когато нямате тази уникална колона във вашите данни и трябва да търсите в няколко колони, за да съответстват на стойност, VLOOKUP не помага.
Така че, за да потърсим стойност в таблица с множество критерии, ще използваме формулата INDEX-MATCH-INDEX.
Обща формула за търсене на множество критерии
= INDEX (lookup_range, MATCH (1, INDEX ((критерии1 = диапазон1)**критерии2 = диапазон2)*(критерииN = диапазонN), 0,1), 0))
lookup_range: Това е диапазонът, от който искате да извлечете стойност.
Критерии1, Критерии2, Критерии N: Това са критериите, които искате да съответствате в диапазон 1, диапазон2 и Обхват N. Можете да имате до 270 критерия - двойки диапазони.
Диапазон1, диапазон2, диапазонN: Това са диапазоните, в които ще отговаряте на съответните критерии.
Как ще работи? Да видим…
INDEX и MATCH с пример за множество критерии
Тук имам таблица с данни. Искам да изтегля името на клиента, използвайки Дата на резервация, Строител и Площ. Тук имам три критерия и един диапазон за търсене.
Напишете тази формула в клетка I4 натиснете Enter.
= INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Как работи:
Вече знаем как работят функциите INDEX и MATCH в EXCEL, така че няма да обяснявам това тук. Ще говорим за трика, който използвахме тук.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Основната част е тази. Всяка част от това изявление връща масив от true false.
Когато булевите стойности се умножат, те връщат масив от 0 и 1. Умножението работи като оператор AND. Следователно, когато всички стойности са верни, тогава той връща 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Това като цяло ще се върне
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}* {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; ; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Което ще се преведе в
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
ИНДЕКС((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Функцията INDEX ще върне същия масив ({0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) към MATCH функция като масив за търсене.
СЪВПАДА(1,ИНДЕКС((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Функцията MATCH ще търси 1 в масив {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}. И ще върне индексния номер на първите 1, намерени в масива. Което е 8 тук.
ИНДЕКС(E2: E16,СЪВПАДА(1,ИНДЕКС((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Накрая INDEX ще върне стойността от зададения диапазон (E2: E16) при намерени индекс (8).
Просто ????. За съжаление, не може да се направи по -просто.
Решение за масив
Ако можете да натиснете CTRL + SHIFT + ENTER последователно, тогава можете да премахнете вътрешната функция INDEX. Просто напишете тази формула и натиснете CTRL + SHIFT + ENTER.
= ИНДЕКС (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Формула за общ масив за търсене на множество критерии
= INDEX (диапазон на търсене, MATCH (1, (критерии1 = диапазон1)*(критерии2 = диапазон2)*(критерииN = диапазонN), 0))
Формулата работи по същия начин като горното обяснение.
Опитах се да го обясня възможно най -просто. Но ако не бях достатъчно ясен, кажете ми го в секцията за коментари по -долу. Между другото, не е нужно да знаете как работи двигателят, за да управлявате кола. Просто трябва да знаете как да го карате. И ти го знаеш много добре.
Как да търсите топ 5 стойности с дублирани стойности, използвайки INDEX-MATCH в Excel
Как да VLOOKUP множество стойности в Excel
Как да VLOOKUP с Dynamic Col Index в Excel
Как да използвате VLOOKUP от две или повече таблици за търсене в Excel
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност
Как да използвате функцията VLOOKUP в Excel
Как да използвате функцията COUNTIF в Excel
Как да използвате функцията SUMIF в Excel