Поиск ближайшего совпадения по нескольким критериям

Иногда нужно найти ближайшее или приблизительное совпадение по нескольким критериям одновременно. Сочетание функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ позволяет быстро решить эту задачу в Excel.

Как найти ближайшее совпадение по нескольким критериям?

Как показано на скриншоте ниже, необходимо выбрать подходящего кандидата на должность по двум критериям: «специальность — Компьютер» и «стаж работы — 15 лет».

Примечание: чтобы формула работала корректно, если в списке есть дублирующиеся специальности, стаж работы по ним должен быть отсортирован по возрастанию.

1. Выберите пустую ячейку для вывода результата, скопируйте в неё приведённую ниже формулу и нажмите клавиши Ctrl+Shift+Enter, чтобы получить результат.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

Примечания:к этой формуле:

• D3:D8— это диапазон столбца, содержащий результат, который вы ищете;
• G5содержит второй критерий (стаж 15), по которому будет выполняться поиск значения;
• G4содержит первый критерий («Компьютер»), по которому будет выполняться поиск значения;
• B3:B8— это диапазон ячеек, соответствующих первому критерию;
• C3:C8— это диапазон ячеек, соответствующих второму критерию;
• Число 1означает приблизительный поиск: если точное значение не найдено, функция вернёт наибольшее значение, меньшее искомого;
• Эту формулу необходимо вводить как формулу массива с помощью клавиш Ctrl+Shift+Enter.

Как работает эта формула

Эту формулу можно разбить на несколько компонентов:

• IF(B3:B8=G4,C3:C8): функция ЕСЛИ возвращает результат в виде {9;13;FALSE;FALSE;FALSE;FALSE}, полученный путём сравнения значений из диапазона B3:B8 со значением в ячейке G4. При совпадении выводится соответствующее значение из диапазона C3:C8, а при несовпадении — ЛОЖЬ. В данном случае обнаружено два совпадения и четыре несовпадения.
• Формула массива =MATCH(G5,{9,13,FALSE,FALSE,FALSE,FALSE},1): функция ПОИСКПОЗ определяет позицию числа 15 (значение из ячейки G5) в заданном массиве. Поскольку число 15 отсутствует, функция возвращает позицию ближайшего меньшего значения — 13. В результате формула выдаёт значение 2.
• А также =INDEX(D3:D8,2): функция ИНДЕКС возвращает значение второй ячейки в диапазоне D3:D8. Окончательный результат — Эми.

Связанные функции

Связанные статьи