Obecný vzorec
=XLOOKUP("*"&value&"*",lookup,results,,2)
souhrn
Chcete-li použít XLOOKUP k porovnání hodnot, které obsahují konkrétní text, můžete použít zástupné znaky a zřetězení. V zobrazeném příkladu je vzorec v F5:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
kde kód (B5: B15) a množství (C5: C15) jsou pojmenované rozsahy.
Vysvětlení
Funkce XLOOKUP obsahuje integrovanou podporu zástupných znaků, ale tuto funkci je nutné explicitně povolit nastavením režimu shody na číslo 2.
V zobrazeném příkladu je XLOOKUP nakonfigurován tak, aby odpovídal hodnotě zadané v buňce E5, která se může objevit kdekoli v hodnotách vyhledávání v B5: B15. Vzorec v F5 je:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, s hvězdičkami (*) zřetězenými vpředu a vzadu
- lookup_array - pojmenovaný kód rozsahu (B5: B15)
- return_array - pojmenované množství rozsahu (C5: C15)
- if_not_found - řetězec "žádná shoda"
- match_mode - k dispozici jako 2 (zástupný znak)
- režim vyhledávání - není k dispozici. Výchozí hodnota je 1 (první až poslední)
Aby byla shoda typu „obsahuje“ automatická, je k hodnotě v buňce E5 se zřetězením přidána hvězdička (*):
"*"&E5&"*"
Po zřetězení se vzorec změní na:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP vyhledá první shodu obsahující „BCC“ (050-BCC-123 v řádku 10) a vrátí odpovídající hodnotu z návratového pole 50.
Všimněte si, že XLOOKUP nerozlišuje velká a malá písmena, zadání „bcc“ do E5 vrátí stejný výsledek:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Níže je uvedena možnost konfigurace XLOOKUPu pro shodu velkých a malých písmen.
Možnost VLOOKUP
Vzorec VLOOKUP také podporuje zástupné znaky, pokud je nastaven na přesnou shodu. Ekvivalentní vzorec VLOOKUP pro tento příklad je:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Úplné vysvětlení zde.
S VYHLEDÁVÁNÍ a NAJÍT
K provedení shody typu „obsahuje“ s XLOOKUP je také možné použít funkce SEARCH a FIND. U shody bez rozlišování velkých a malých písmen (jako v předchozím příkladu) můžete použít HLEDAT takto:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
U shody rozlišující velká a malá písmena můžete místo toho použít FIND:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Obě výše uvedené možnosti usnadňují rozšíření kritérií tak, aby zahrnovaly další podmínky pomocí logické logiky.
Logika pro ISNUMBER + SEARCH je vysvětlena zde.
Více zápasů
Pokud potřebujete více shod, podívejte se na funkci FILTER.
