Üldine valem
=XLOOKUP(1,--EXACT(range1,"RED"),range2)
Kokkuvõte
Suur- ja väiketähtedega täpse vaste loomiseks võite kasutada funktsiooni XLOOKUP funktsiooniga EXACT. Näidatud näites on valem F5-s järgmine:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15)
mis sobib tähega "RED" (tõstutundlik) ja tagastab kogu rea.
Selgitus
Iseenesest pole funktsioon XLOOKUP tõstutundlik. Otsingu väärtus "PUNANE" sobib "punase", "punase" või "punase" väärtusega. Selle piirangu saame ümber lahendada, ehitades XLOOKUPile sobiva otsingu massiivi loogilise avaldisega.
Töötades seestpoolt väljapoole, et anda XLOOKUP'ile võimalus juhtumite sobitamiseks, kasutame funktsiooni EXACT täpselt nii:
EXACT(B5:B15,"RED") // test for "RED"
Kuna vahemikus E5 on D5 11 väärtust, tagastab EXACT massiivi, mille tulemuseks on 11 TRUE FALSE:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Pange tähele, et TRUE asukoht vastab reale, kus värv on "PUNANE".
Lühiduse huvides (ja loogika hõlpsaks laiendamiseks loogika loogikaga) sunnime TRUE FALSE väärtused topeltnegatiiviga 1s ja 0s:
--EXACT(B5:B15,"RED") // convert to 1s and 0s
mis annab sellise massiivi:
(0;0;0;0;1;0;0;0;0;0;0)
Pange tähele, et positsioon 1 vastab reale, kus värv on "PUNANE". See massiiv tagastatakse otsingu massiivi argumendina otse funktsioonile XLOOKUP.
Nüüd saame lihtsalt valemiga:
=XLOOKUP(1,(0;0;0;0;1;0;0;0;0;0;0),B5:D15)
Otsingu väärtusega 1 leiab XLOOKUP 1 5. kohal ja tagastab tagasimassiivis 5. rea B9: D9.
Loogika laiendamine
Loogika ülesehitust saab hõlpsasti laiendada. Näiteks aprilli kuu vaste kitsendamiseks PUNASEKS saate kasutada järgmist valemit:
=XLOOKUP(1,EXACT(B5:B15,"RED")*(MONTH(C5:C15)=4),B5:D15)
Siinkohal, kuna mõlemad avaldised tagastavad reaalsete FALSE väärtuste massiivi ja kuna need massiivid korrutatakse kokku, sunnib matemaatikaoperatsioon TRUE ja FALSE väärtuseks 1 ja 0. Topeltnegatiivi pole vaja kasutada.
Kuna otsingu väärtus jääb 1, nagu ülaltoodud valemis.
Esimene ja viimane matš
Mõlemad ülaltoodud valemid tagastavad andmekogumis sõna "RED" esimese vaste. Kui vajate viimast vastet, saate pöördotsingu teha, seadistades XLOOKUP-i otsingurežiimi argumendi väärtusele -1:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15,,,-1) // last match
Kui peate tagastama mitme vaste tulemused, vaadake funktsiooni FILTER.
