Общая формула
=INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0))
Резюме
Чтобы получить первое совпадение в двух диапазонах значений, можно использовать формулу, основанную на функциях ИНДЕКС, ПОИСКПОЗ и СЧЁТЕСЛИ. В показанном примере формула в G5:
=INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0))
где диапазон1 - именованный диапазон B5: B8, диапазон2 - именованный диапазон D5: D7.
Объяснение
В этом примере именованный диапазон «диапазон1» относится к ячейкам B5: B8, а именованный диапазон «диапазон2» относится к D5: D7. Мы используем именованные диапазоны только для удобства и удобочитаемости; формула также отлично работает с обычными ссылками на ячейки.
Ядро этой формулы - ИНДЕКС и ПОИСКПОЗ. Функция ИНДЕКС извлекает значение из диапазона2, которое представляет первое значение в диапазоне2, которое находится в диапазоне1. Для функции ИНДЕКС требуется индекс (номер строки), и мы генерируем это значение с помощью функции ПОИСКПОЗ, для которой установлено соответствие значению ИСТИНА в этой части формулы:
MATCH(TRUE,COUNTIF(range1,range2)>0,0)
Здесь значение совпадения - ИСТИНА, и массив поиска создается с COUNTIF здесь:
COUNTIF(range1,range2)>0
СЧЁТЕСЛИ возвращает количество значений диапазона 2, которые появляются в диапазоне 1. Поскольку диапазон 2 содержит несколько значений, СЧЁТЕСЛИ вернет несколько результатов, которые выглядят следующим образом:
(0;0;1)
Мы используем "> 0", чтобы заставить все результаты иметь значение ИСТИНА или ЛОЖЬ:
(FALSE;FALSE;TRUE)
Затем ПОИСКПОЗ делает свое дело и возвращает позицию первого ИСТИНА (если есть), которое появляется, в данном случае числа 3.
Наконец, ИНДЕКС возвращает значение в этой позиции «Красный».
