Splošna formula
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Povzetek
Če želite v celici preveriti eno od več stvari in vrniti prvo ujemanje na seznamu, lahko uporabite formulo INDEX / MATCH, ki za iskanje ujemanja uporablja ISKANJE ali NAJDI. V prikazanem primeru je formula v C5:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kjer je "stvari" imenovani obseg E5: E9.
Opomba: to je matrična formula, ki jo je treba vnesti s tipkama Control + Shift + Enter.
Pojasnilo
V tem primeru imamo seznam barv v imenovanem obsegu, imenovanem "stvari" (E5: E9). Besedilo v stolpcu B želimo preveriti, ali vsebuje katero od teh barv. V tem primeru želimo vrniti ime prve najdene barve.
Ta formula, ki deluje od znotraj navzven, uporablja funkcijo ISNUMBER in SEARCH za iskanje besedila v B5 za vsako barvo, navedeno v "stvareh", kot je ta:
ISNUMBER(SEARCH(things,B5)
Ta izraz temelji na formuli (tukaj je podrobno razloženo), ki preverja celico za posamezen podniz. Če celica vsebuje podniz, izraz vrne TRUE. V nasprotnem primeru izraz vrne FALSE.
Ko temu ISKANJU damo seznam stvari (namesto ene stvari), nam bo vrnil vrsto rezultatov. Vsaka najdena barva bo ustvarila številski položaj, barve, ki jih ne najdemo, pa napako:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Nato funkcija ISNUMBER pretvori rezultate v TRUE / FALSE vrednosti. Vsaka številka postane TRUE in vsaka napaka (ni najdena) postane FALSE. Rezultat je matrika, kot je ta:
(FALSE;FALSE;TRUE;FALSE;FALSE)
To polje se vrne v funkcijo MATCH kot argument polja. Vrednost iskanja je TRUE, vrsta ujemanja pa je nastavljena na nič, da se doseže natančno ujemanje. Ko je ujemajoča se barva, MATCH vrne položaj prvega TRUE najdeno. Ta vrednost se vnese v funkcijo INDEX kot številka vrstice, kot matrika pa je naveden imenovani obseg "stvari". Ko je vsaj eno ujemanje, INDEX vrne barvo na tem mestu. Ko ni mogoče najti nobenega ujemanja, ta formula vrne napako # N / A.
S trdo kodiranimi vrednostmi
Če v tem primeru ne želite nastaviti zunanjega imenovanega obsega, kot so »stvari«, lahko vrednosti trdno kodirate v formulo kot »konstante matrike«, kot je ta:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Pridobite prvo tekmo v celici
Jezik tukaj je precej zmeden, toda zgornja formula bo vrnila prvo ujemanje na seznamu stvari, ki jih je treba iskati. Če namesto tega želite vrniti prvo ujemanje v preskušeni celici, lahko poskusite s formulo, kot je ta:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
V tej različici formule je funkcija MATCH nastavljena tako, da išče rezultat tega delčka:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
ki s pomočjo funkcije AGGREGATE dobi najmanjšo vrednost v rezultatih, ki jih vrne SEARCH. Tukaj potrebujemo AGREGATE, ker bo dohodna matrika verjetno vsebovala napake (vrne jih ISKANJE, kadar stvari niso najdene), in potrebujemo funkcijo, ki bo te napake prezrla in nam vseeno dala najmanjšo številsko vrednost.
Rezultat iz AGGREGATE se vrne neposredno v MATCH kot iskalno vrednost, skupaj z isto matriko, ki jo vrne SEARCH. Končni rezultat je prvo ujemanje v celici in ne prvo ujemanje na seznamu stvari.
