Bendroji formulė
=XLOOKUP("*"&value&"*",lookup,results,,2)
Santrauka
Norėdami naudoti XLOOKUP, kad atitiktų reikšmes, kuriose yra konkretus tekstas, galite naudoti pakaitos simbolius ir sujungimą. Parodytame pavyzdyje formulė F5 yra:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
kur kodas (B5: B15) ir kiekis (C5: C15) įvardijami diapazonai.
Paaiškinimas
XLOOKUP funkcijoje yra įmontuotas pakaitinių simbolių palaikymas, tačiau ši funkcija turi būti aiškiai įjungta nustatant atitikties režimą į skaičių 2.
Parodytame pavyzdyje XLOOKUP sukonfigūruotas taip, kad atitiktų langelyje E5 įvestą vertę, kuri gali būti rodoma bet kurioje B5: B15 paieškos reikšmėje. F5 formulė yra:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, priekyje ir gale sujungtos žvaigždutėmis (*)
- lookup_array - nurodytas diapazono kodas (B5: B15)
- return_array - nurodytas diapazono dydis (C5: C15)
- if_not_found - eilutė „neatitinka“
- match_mode - pateikiama kaip 2 (pakaitos atitikmuo)
- search_mode - nepateiktas. Pagal numatytuosius nustatymus 1 (pirmas paskutinis)
Norint, kad atitikimas „sudėtyje“ būtų automatinis, pakaitos žvaigždutė (*) yra pridedama ir pridedama prie E5 langelio vertės, sujungiant:
"*"&E5&"*"
Po sujungimo formulė tampa:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
„XLOOKUP“ nustato pirmąją atitiktį, kurioje yra „BCC“ (050-BCC-123 10 eilutėje), ir grąžina atitinkamą reikšmę iš grįžimo masyvo 50.
Atkreipkite dėmesį, kad „XLOOKUP“ neskiria didžiųjų ir mažųjų raidžių, įvedus „bcc“ į E5, bus gautas tas pats rezultatas:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Žemiau pateikiama parinktis sukonfigūruoti XLOOKUP didžiosioms ir mažosioms raidėms.
VLOOKUP parinktis
VLOOKUP formulė taip pat palaiko pakaitos simbolius, kai nustatyta tiksli atitiktis. Šio pavyzdžio ekvivalentinė VLOOKUP formulė yra:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Visas paaiškinimas čia.
Su PAIEŠKA ir RASTI
Taip pat galima naudoti funkcijas PAIEŠKA ir RASTI, norint atlikti „yra“ tipo atitikimą su XLOOKUP. Jei neatitinkate didžiųjų ir mažųjų raidžių (pvz., Aukščiau pateiktame pavyzdyje), galite naudoti SEARCH taip:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Skiriant didžiosioms ir mažosioms raidėms, galite naudoti FIND:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Abi pirmiau pateiktos parinktys palengvina kriterijų išplėtimą įtraukiant kitas sąlygas, naudojant loginę logiką.
ISNUMBER + SEARCH logika paaiškinta čia.
Kelios rungtynės
Jei jums reikia kelių atitikmenų, žr. FILTRO funkciją.
