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

В некоторых случаях вам может понадобиться найти ближайшее или приблизительное значение на основе нескольких критериев. С помощью комбинации функций INDEX, MATCH и IF вы можете быстро выполнить эту задачу в Excel.

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

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

Примечание: Для корректной работы, если есть дубликаты специальностей, опыт работы этих дубликатов должен быть отсортирован в порядке возрастания.

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

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

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

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

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

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

• IF(B3:B8=G4,C3:C8): здесь функция IF возвращает результат как {9;13;FALSE;FALSE;FALSE;FALSE}, который получен путем проверки значений в B3:B8 на соответствие значению в G4. Если есть совпадение, возвращается соответствующее значение, иначе возвращается FALSE. Здесь найдено два совпадения и четыре несовпадения.
• Массивная формула =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): функция MATCH находит позицию числа 15 (значение в G5) в диапазоне C3:C8. Поскольку число 15 не найдено, оно совпадает со следующим меньшим значением 13. Таким образом, результат здесь равен 2.
• И =INDEX(D3:D8,2): Функция INDEX возвращает значение второй ячейки в диапазоне D3:D8. Таким образом, окончательный результат — Amy.

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

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