Обща формула
=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
Ако трябва да върнете резултати от множество съвпадения, вижте функцията FILTER.
