Загальна формула
=INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0))
Резюме
Щоб отримати перший збіг у двох діапазонах значень, ви можете використовувати формулу на основі функцій INDEX, MATCH та COUNTIF. У наведеному прикладі формула в G5 має вигляд:
=INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0))
де "діапазон1" - іменований діапазон B5: B8, "діапазон2" - іменований діапазон D5: D7.
Пояснення
У цьому прикладі названий діапазон "діапазон1" відноситься до комірок B5: B8, а названий діапазон "діапазон2" відноситься до D5: D7. Ми використовуємо іменовані діапазони лише для зручності та читабельності; формула чудово працює і зі звичайними посиланнями на клітинки.
Основою цієї формули є INDEX та MATCH. Функція INDEX отримує значення з діапазону2, яке представляє перше значення в діапазоні2, яке знаходиться в діапазоні1. Функція INDEX вимагає індекс (номер рядка), і ми генеруємо це значення за допомогою функції MATCH, яка встановлена відповідно до значення TRUE у цій частині формули:
MATCH(TRUE,COUNTIF(range1,range2)>0,0)
Тут значення збігу має значення TRUE, а масив підстановки створюється тут із COUNTIF:
COUNTIF(range1,range2)>0
COUNTIF повертає підрахунок значень діапазону2, які відображаються в діапазоні1. Оскільки range2 містить кілька значень, COUNTIF поверне кілька результатів, які виглядають так:
(0;0;1)
Ми використовуємо "> 0", щоб примусити всі результати до TRUE або FALSE:
(FALSE;FALSE;TRUE)
Потім MATCH робить своє і повертає позицію першої TRUE (якщо така є), яка з’являється, в даному випадку число 3.
Нарешті, INDEX повертає значення в цій позиції, "Червоне".
