Kokkuvõte
Mitmel keerulisel kriteeriumil põhinevate andmete otsimiseks võite kasutada funktsiooni XLOOKUP mitme boolean loogikal põhineva avaldisega. Näidatud näites on valem G5-s järgmine:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
XLOOKUPi vastesätete (täpne) ja otsingurežiimi (esimesest viimaseni) vaikeseadetega sobib valem esimese kirjega, kus
konto algab tähega "x" JA piirkond on "ida" ja kuu EI OLE aprill.
mis on näidatud näite neljas kirje (rida 8).
Selgitus
Tavaliselt on funktsioon XLOOKUP konfigureeritud otsima töölehel oleva otsingumassiivi väärtust. Kui aga väärtuse sobitamiseks kasutatavad kriteeriumid muutuvad keerukamaks, saate tõeväärtusloogika abil luua sirvimisel ainult 1-st ja 0-st koosneva otsimassiivi ja seejärel otsida väärtust 1. Selles näites kasutatakse sellist lähenemist:
=XLOOKUP(1,boolean_array,result_array)
Selles näites on nõutavad kriteeriumid järgmised:
konto algab tähega "x" JA piirkond on "ida" ja kuu EI OLE aprill.
Kõigi ülaltoodud kolme kriteeriumi puhul kasutame eraldi loogilist avaldist. Esimene avaldis kasutab funktsiooni LEFT, et testida, kas konto algab tähega "x":
LEFT(B5:B16)="x" // account begins with "x"
Kuna kontrollime kaksteist väärtust, on tulemuseks massiiv kaheteistkümne väärtusega:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Teine avaldis testib, kas piirkond on "ida", kasutades operaatorit võrdne (=):
C5:C16="east" // region is east
Nagu varemgi, saame veel ühe massiivi kaheteistkümne TRUE FALSE väärtusega:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Kolmas väljend peab välistama aprillikuu. Lihtsaim viis seda teha on testida aprillikuu funktsiooni MONTH otse:
MONTH(D5:D16)=4 // month is April
Seejärel kasutage tulemuse ümberpööramiseks funktsiooni EI:
NOT(MONTH(D5:D16)=4) // month is not April
mis loob massiivi, mis kirjeldab õigesti sõna "mitte aprill":
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Järgmisena korrutatakse kõik kolm massiivi kokku ja matemaatikaoperatsioon sunnib TÕENE ja VALE väärtused väärtusele 1 ja 0
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
Boole'i aritmeetikas töötab korrutamine nagu loogiline funktsioon JA, nii et lõpptulemuseks on üks selline massiiv:
(0;0;0;1;1;0;0;0;0;1;0;1)
Valemi saab nüüd ümber kirjutada järgmiselt:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
Kui otsingu väärtus on 1 ning vaste režiimi (täpne) ja otsingurežiimi (esimesest viimaseni) vaikesätted, vastab XLOOKUP esimesele 1 (neljas positsioon) ja tagastab tulemuste massiivi vastava rea, milleks on B8: E8.
Viimane matš
Kui määrate valikulise otsingurežiimi argumendi -1, saate leida "viimase vaste" samade kriteeriumidega:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
