Üldine valem
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Kokkuvõte
Lahtrite kontrollimiseks mitmest asjast ja loendis esimese vaste tagastamiseks võite kasutada valemit INDEX / MATCH, mis kasutab vaste otsimiseks otsingu või otsimise funktsiooni. Näidatud näites on valem C5-s:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kus "asjad" on nimega vahemik E5: E9.
Märkus: see on massiivi valem ja see tuleb sisestada klahvidega Control + Tõst + Enter.
Selgitus
Selles näites on meil värvide loetelu nimega vahemikus, mida nimetatakse asjadeks (E5: E9). Tahame kontrollida veeru B teksti, kas see sisaldab mõnda neist värvidest. Kui jah, siis tagastame esimese leitud värvi nime.
Seestpoolt edasi töötades kasutab see valem funktsiooni ISNUMBER ja OTSI, et otsida B5-st teksti iga värvi kohta, mis on loetletud järgmistes asjades:
ISNUMBER(SEARCH(things,B5)
See avaldis põhineb valemil (mida on siin üksikasjalikult selgitatud), mis kontrollib rakku ühe alamstringi suhtes. Kui lahter sisaldab alamstringi, tagastab avaldis TÕENE. Kui ei, tagastab avaldis FALSE.
Kui anname sellele otsingule loendi asjadest (ühe asja asemel), annab see meile tagasi hulga tulemusi. Iga leitud värv loob arvulise positsiooni ja värvid, mida ei leitud, tekitavad vea:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Funktsioon ISNUMBER teisendab seejärel tulemused väärtuseks TRUE / FALSE. Mis tahes arv muutub TÕELISEKS ja mis tahes viga (ei leitud) muutub VALE. Tulemuseks on selline massiiv:
(FALSE;FALSE;TRUE;FALSE;FALSE)
See massiiv tagastatakse massiivi argumendina funktsioonile MATCH. Otsitava väärtuse väärtus on TRUE ja täpse vaste sundimiseks on vastetüüp null. Kui värv sobib, tagastab MATCH esimese leitud Positsioon. See väärtus sisestatakse funktsiooni INDEX rea numbrina, massiivina pakutakse nimega vahemikku "asjad". Kui on vähemalt üks vaste, tagastab INDEX selle asukoha värvi. Kui vastet ei leitud, tagastab see valem vea # N / A.
Kõvakodeeritud väärtustega
Kui te ei soovi selles näites seadistada välist nimega vahemikku, näiteks "asjad", saate väärtused valemisse kodeerida järgmiselt: "massiivikonstandid":
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Hankige esimene vaste lahtris
Siinne keel on üsna segane, kuid ülaltoodud valem tagastab esimese otsitava asjade loendist leitud vaste. Kui soovite selle asemel tagastada testitavast lahtrist leitud esimese vaste, võite proovida järgmist valemit:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Selles valemiversioonis on funktsioon MATCH seadistatud selle sisulõike tulemuse otsimiseks:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
mis kasutab funktsiooni AGGREGATE, et saada SEARCHi tagastatud tulemuste minimaalne väärtus. Meil on siin vaja AGGREGATE-d, sest sissetulev massiiv sisaldab tõenäoliselt vigu (mille SEARCH tagastab, kui asju ei leita), ja me vajame funktsiooni, mis ignoreerib neid vigu ja annab meile siiski minimaalse arvulise väärtuse.
AGGREGATE tulemus tagastatakse otsingu väärtusena otse mängu MATCH koos sama massiividega, mille tagastab otsing. Lõpptulemuseks on lahtrist esimene vaste, mitte esimene asjade loendist leitud vaste.
