Общая формула
(=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num))
Резюме
Поиск с учетом регистра
По умолчанию при стандартном поиске с помощью VLOOKUP или INDEX + MATCH регистр не учитывается. И ВПР, и ПОИСКПОЗ просто вернут первое совпадение, игнорируя регистр.
Однако, если вам нужно выполнить поиск с учетом регистра, вы можете сделать это с помощью формулы массива, которая использует ИНДЕКС, ПОИСКПОЗ и функцию ТОЧНОСТЬ.
В этом примере мы используем следующую формулу
(=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3))
Эта формула представляет собой формулу массива, и ее необходимо вводить с помощью Control + Shift + Enter.
Объяснение
Поскольку только ПОИСКПОЗ не чувствительно к регистру, нам нужен способ заставить Excel сравнивать регистр. Функция EXACT - идеальная функция для этого, но то, как мы ее используем, несколько необычно, потому что нам нужно сравнить одну ячейку с диапазоном ячеек.
Работая изнутри наружу, мы сначала имеем:
EXACT(F4,B3:B102)
где F4 содержит значение поиска, а B3: B102 - это ссылка на столбец поиска (имена). Поскольку мы даем ТОЧНЫЙ массив в качестве второго аргумента, мы вернем массив ИСТИННЫХ ложных значений, например:
(ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, ИСТИНА и т. Д.)
Это результат сравнения значения B4 в каждой ячейке столбца подстановки. Где бы мы ни увидели ИСТИННО, мы знаем, что у нас есть точное совпадение с учетом регистра.
Теперь нам нужно получить позицию (т.е. номер строки) ИСТИННОГО значения в этом массиве. Для этого мы можем использовать MATCH, ища TRUE и устанавливая режим точного совпадения:
MATCH(TRUE,EXACT(F4,B3:B102),0)
Важно отметить, что MATCH всегда будет возвращать первое совпадение, если есть дубликаты, поэтому, если в столбце будет другое точное совпадение, вы найдете только первое совпадение.
Теперь у нас есть номер строки. Затем нам просто нужно использовать INDEX, чтобы получить значение на правом пересечении строки и столбца. Номер столбца в этом случае жестко задан как 3, поскольку данные именованного диапазона включают все столбцы. Итоговая формула:
(=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3))
Мы должны ввести эту формулу как формулу массива из-за массива, созданного EXACT.
Эта формула будет извлекать как текстовые, так и числовые значения. Если вы хотите получать только числа, вы можете использовать формулу на основе СУММПРОИЗВ; см. ссылку ниже
