Загальна формула
=XLOOKUP(1,--EXACT(range1,"RED"),range2)
Резюме
Щоб створити точну відповідність регістру, ви можете використовувати функцію XLOOKUP із функцією EXACT. У наведеному прикладі формула в F5 має вигляд:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15)
який відповідає "ЧЕРВОНОМУ" (з урахуванням регістру) і повертає весь рядок.
Пояснення
Сама по собі функція XLOOKUP не враховує регістр. Значення пошуку "ЧЕРВОНИЙ" збігатиметься з "червоним", "ЧЕРВОНИМ" або "Червоним". Ми можемо обійти це обмеження, створивши відповідний масив пошуку для XLOOKUP з логічним виразом.
Працюючи зсередини, щоб надати XLOOKUP можливість відповідати регістру, ми використовуємо функцію EXACT так:
EXACT(B5:B15,"RED") // test for "RED"
Оскільки в діапазоні E5: D15 є 11 значень, EXACT повертає масив з 11 результатами TRUE FALSE, таким чином:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Зверніть увагу, що положення TRUE відповідає рядку, де колір "ЧЕРВОНИЙ".
Для стислості (і для того, щоб логіку можна було легко розширити за допомогою логічної логіки), ми примушуємо значення TRUE FALSE до 1s і 0s з подвійним від’ємником:
--EXACT(B5:B15,"RED") // convert to 1s and 0s
який видає такий масив:
(0;0;0;0;1;0;0;0;0;0;0)
Зверніть увагу, що позиція 1 відповідає рядку, де колір "ЧЕРВОНИЙ". Цей масив повертається безпосередньо до функції XLOOKUP як аргумент пошукового масиву.
Тепер ми можемо просто формулу:
=XLOOKUP(1,(0;0;0;0;1;0;0;0;0;0;0),B5:D15)
З пошуковим значенням 1 XLOOKUP знаходить 1 на 5-й позиції та повертає 5-й рядок у масиві повернення, B9: D9.
Розширення логіки
Структуру логіки можна легко розширити. Наприклад, щоб звузити відповідність до "ЧЕРВОНОГО" у квітні, можна скористатися такою формулою:
=XLOOKUP(1,EXACT(B5:B15,"RED")*(MONTH(C5:C15)=4),B5:D15)
Тут, оскільки кожен з двох виразів повертає масив значень TRUE FALSE і оскільки ці масиви множаться разом, математична операція примушує значення TRUE і FALSE до 1s і 0s. Не потрібно використовувати подвійний негатив.
Оскільки значення підстановки залишається 1, як у формулі вище.
Перший та останній матчі
Обидві наведені вище формули повернуть перший збіг "ЧЕРВОНОГО" у наборі даних. Якщо вам потрібен останній збіг, ви можете виконати зворотний пошук, встановивши аргумент режиму пошуку для XLOOKUP на -1:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15,,,-1) // last match
Якщо вам потрібно повернути результати з кількох збігів, див. Функцію ФІЛЬТР.
