Для того, чтобы найти приближенное соответствие на основе более одного критерия, вы можете использовать формулу массива, основанную на ИНДЕКС и ПОИСКПОЗ, с помощью функции ЕСЛИ.
Пример формулы в G7:
{ = ИНДЕКС (D5:D10; ПОИСКПОЗ (G6; ЕСЛИ( B5:B10 = G5; С5:С10);1))}
Целью данной формулы является найти размер кошки, если известен ее вес.
По сути, это просто формула ИНДЕКС/ПОИСКПОЗ. Проблемой в данном случае является то, что нам нужно «отсеивать» посторонние записи в таблице.
Это делается с помощью простой функции ЕСЛИ:
ЕСЛИ( B5:B10 = G5; С5:С10 )
Она входит в функцию ПОИСКПОЗ как массив. Значение поиска для совпадений происходит от G6, который содержит вес (7 кг в примере).
Обратите внимание, что совпадение настроено для приблизительного совпадения, установив тип_сопоставления = 1, это нужно сортировки С5:С10.
ПОИСКПОЗ возвращает позицию веса в массиве, и передается ИНДЕКСУ как номер строки. Поисковый_массив для ИНДЕКСА размеры в D5:D10, так ИНДЕКС получает размер, соответствующий положению генерируемого совпадением (номер 6 в показанном примере).
Базовый ИНДЕКС ПОИСКПОЗ с приближенным сопоставлением
= ИНДЕКС( класс; ПОИСКПОЗ( балл; баллы; 1))
Этот пример показывает, как использовать ИНДЕКС и ПОИСКПОЗ для получения класса из таблицы на основе заданного балла. Для этого требуется «приближенное соответствие», так как маловероятно , что реальная оценка существует в таблице.
Обратите внимание , что последний аргумент 1 (эквивалент ИСТИНЫ), что позволяет ПОИСКПОЗ выполнить приблизительное совпадение на значения , перечисленные в порядке возрастания. В этой конфигурации, ПОИСКПОЗ возвращает позицию первого значения, которое меньше или равно значению перекодировки.
Базовый ИНДЕКС ПОИСКПОЗ, точное совпадение
= ИНДЕКС( данные; ПОИСКПОЗ( значение; поиск_столбца ; ЛОЖЬ); столбец )
Эта формула использует ПОИСКПОЗ, чтобы получить позицию строки «Истории Игрушек» в таблице, и ИНДЕКС для извлечения значения в этой строке в колонке 2.
Обратите внимание, что последний аргумент имеет значение 0, что заставляет ПОИСКПОЗ найти точное совпадение.
ИНДЕКС с одним столбцом
В приведенном выше примере, ИНДЕКС получает массив, который содержит все данные в таблице. Тем не менее, вы можете легко переписать формулы для работы только с одним столбцом, что избавляет от необходимости указывать номер столбца:
= ИНДЕКС (С5:C9; ПОИСКПОЗ (H4; B5:B9;0)) // год
= ИНДЕКС (D5:D9; ПОИСКПОЗ (H4; B5:B9;0)) // ранг
= ИНДЕКС (E5:E9; ПОИСКПОЗ (H4; B5:B9;0)) // продажи
В каждом случае ИНДЕКС принимает массив одного столбца, который соответствует данным его извлечений, и ПОИСКПОЗ поставляет номер строки.
Чувствительное к регистру совпадение
{ = ПОИСКПОЗ (ИСТИНА; СОВПАД (диапазон; значение); 0)}
Для выполнения чувствительного к регистру совпадения, вы можете использовать функцию СОВПАД вместе с ПОИСКПОЗ в формуле массива.
Сама по себе функция ПОИСКПОЗ не чувствительна к регистру, поэтому следующая формула получает 1:
= ПОИСКПОЗ («ИВАН»; B5:B11; 0 )
Чтобы добавить чувствительность к регистру, мы используем функцию СОВПАД:
СОВПАД ( B5:B11; E4 )
Которая получает массив истина/ложь значения:
{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}
Этот массив переходит в функции ПОИСКПОЗ как массив. Для поиска, мы используем значение ИСТИНА с ПОИСКПОЗ, установленным в режим точного соответствия путем установки тип_сопоставления к нулю.
= ПОИСКПОЗ (ИСТИНА; { ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}; 0 )
ПОИСКПОЗ затем получает позицию первого найденного истинного значения: 4.
Точный поиск соответствия с ИНДЕКС и ПОИСКПОЗ
{= ИНДЕКС (данные;ПОИСКПОЗ (ИСТИНА; СОВПАД(текст1; текст2 );0 ); номер_столбца )}
Если вам нужно сделать, чувствительный к регистру поиск, вы можете сделать это с помощью формулы массива, которая использует ИНДЕКС, ПОИСКПОЗ и СОВПАД функции.
В примере, мы используем следующую формулу
={ИНДЕКС (B5:D12; ПОИСКПОЗ (ИСТИНА; СОВПАД (F5;B5:B12);0);3)}
Эта формула будет извлекать текст и числовые значения. Если вы хотите получить только числа, вы можете использовать формулу, основанную на СУММПРОИЗВ.