Обща формула
(=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num))
Обобщение
Справка, чувствителна към малки и големи букви
По подразбиране стандартните справки с VLOOKUP или INDEX + MATCH не са чувствителни към малки и големи букви. Както VLOOKUP, така и MATCH просто ще върнат първия мач, игнорирайки случая.
Ако обаче трябва да направите справка с чувствителност към малки и големи букви, можете да го направите с формула на масив, която използва функцията INDEX, MATCH и EXACT.
В примера използваме следната формула
(=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3))
Тази формула е формула на масив и трябва да се въведе с Control + Shift + Enter.
Обяснение
Тъй като MATCH сам по себе си не е чувствителен към малки и големи букви, имаме нужда от начин да накараме Excel да сравнява регистъра. Функцията EXACT е перфектната функция за това, но начинът, по който я използваме, е малко необичаен, защото трябва да сравним една клетка с набор от клетки.
Работейки отвътре навън, първо имаме:
EXACT(F4,B3:B102)
където F4 съдържа справочната стойност, а B3: B102 е препратка към справочната колона (Имена). Тъй като даваме EXACT масив като втори аргумент, ще получим обратно масив от TRUE фалшиви стойности като този:
(FALSE, FALSE, FALSE, FALSE, FALSE, TRUE и др.)
Това е резултатът от сравняването на стойността в B4 на всяка клетка в справочната колона. Където и да виждаме TRUE, знаем, че имаме точно съвпадение, което зачита случая.
Сега трябва да получим позицията (т.е. номера на реда) на стойността TRUE в този масив. За това можем да използваме MATCH, търсейки TRUE и да зададем в режим на точно съвпадение:
MATCH(TRUE,EXACT(F4,B3:B102),0)
Важно е да се отбележи, че MATCH винаги ще връща първото съвпадение, ако има дубликати, така че ако в колоната има друго точно съвпадение, ще съответствате само на първото.
Сега имаме номер на ред. След това просто трябва да използваме INDEX, за да извлечем стойността в дясно пресичане на ред и колона. Номерът на колоната в този случай е кодиран твърдо като 3, тъй като посочените данни за диапазон включват всички колони. Крайната формула е:
(=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3))
Трябва да въведем тази формула като формула на масив поради масива, създаден от ТОЧНО.
Тази формула ще извлече както текстови, така и цифрови стойности. Ако искате да извлечете само числа, можете да използвате формула, базирана на SUMPRODUCT; вижте връзката по-долу
