Bendroji formulė
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Santrauka
Norėdami patikrinti langelį, kuriame yra vienas iš kelių dalykų, ir grąžinti pirmąją sąraše rastą atitiktį, galite naudoti INDEX / MATCH formulę, kurioje ieškant arba ieškant ieškokite atitikties. Parodytame pavyzdyje formulė C5 yra:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kur „daiktai“ yra įvardytas diapazonas E5: E9.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Šiame pavyzdyje mes turime spalvų sąrašą pavadintame diapazone, vadinamą „daiktais“ (E5: E9). Norime patikrinti B stulpelio tekstą, ar jame nėra šių spalvų. Jei taip, mes norime grąžinti pirmosios rastos spalvos pavadinimą.
Dirbant iš vidaus, ši formulė naudoja funkciją ISNUMBER ir SEARCH, kad ieškotų B5 tekste kiekvienos spalvos, nurodytos tokiuose dalykuose:
ISNUMBER(SEARCH(things,B5)
Ši išraiška pagrįsta formule (išsamiai paaiškinta čia), kuri tikrina, ar langelyje nėra vieno pakraščio. Jei langelyje yra poskyris, išraiška grąžina TRUE. Jei ne, išraiška pateikia FALSE.
Kai šiai PAIEŠKAI pateiksime daiktų sąrašą (vietoj vieno), mums bus grąžinta daugybė rezultatų. Kiekviena rasta spalva sugeneruos skaitinę padėtį, o nerastos spalvos sukurs klaidą:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Funkcija ISNUMBER konvertuoja rezultatus į TRUE / FALSE reikšmes. Bet koks skaičius tampa TIESA, o bet kokia klaida (nerasta) tampa NETIESA. Rezultatas yra toks masyvas:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Šis masyvas grąžinamas į funkciją MATCH kaip masyvo argumentas. Paieškos vertė yra TIESA, o atitikties tipas yra nustatytas į nulį, kad priverstų tikslią atitiktį. Kai yra atitinkanti spalva, MATCH grąžina pirmąją rastą TRUE poziciją. Ši reikšmė įtraukiama į INDEX funkciją kaip eilutės numerį, nurodant masyvą nurodytam diapazonui „dalykai“. Kai yra bent viena atitiktis, INDEX grąžina spalvą toje vietoje. Neradus atitikties, ši formulė pateikia klaidą # N / A.
Su sunkiai užkoduotomis vertėmis
Jei nenorite šiame pavyzdyje nustatyti išorinio pavadinto diapazono, pvz., „Dalykai“, galite tiksliai suformuoti reikšmes į formulę kaip „masyvo konstantos“:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Gaukite pirmąsias rungtynes langelyje
Kalba čia yra gana paini, tačiau aukščiau pateikta formulė pateiks pirmąjį ieškomų dalykų sąraše rastą atitikmenį. Jei vietoj to norite grąžinti pirmąją atitiktį, rastą bandomame langelyje, galite išbandyti tokią formulę:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Šioje formulės versijoje funkcija MATCH nustatyta nustatyti šio fragmento rezultatą:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
kuri naudoja funkciją AGGREGATE, kad gautų mažiausią reikšmę rezultatuose, kuriuos pateikia SEARCH. Čia mums reikia AGGREGATE, nes gaunamame masyve greičiausiai bus klaidų (kurias SEARCH grąžins, kai ko nerasite), ir mums reikia funkcijos, kuri nepaisys tų klaidų ir vis tiek suteiks mums minimalią skaitinę vertę.
Rezultatas iš AGGREGATE grąžinamas tiesiai į MATCH kaip paieškos vertė kartu su ta pačia masyvu, kurį grąžina SEARCH. Galutinis rezultatas yra pirmoji rasta langelyje, o ne pirmoji rasta daiktų sąraše.
