Excel formel: XLOOKUP match tekst indeholder -

Generisk formel

=XLOOKUP("*"&value&"*",lookup,results,,2)

Resumé

For at bruge XLOOKUP til at matche værdier, der indeholder specifik tekst, kan du bruge jokertegn og sammenkædning. I det viste eksempel er formlen i F5:

=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)

hvor kode (B5: B15) og mængde (C5: C15) er navngivet områder.

Forklaring

XLOOKUP-funktionen indeholder indbygget understøttelse af jokertegn, men denne funktion skal aktiveres eksplicit ved at indstille matchningstilstand til nummer 2.

I det viste eksempel er XLOOKUP konfigureret til at matche den værdi, der er indtastet i celle E5, som kan vises hvor som helst i opslagsværdierne i B5: B15. Formlen i F5 er:

=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
  • lookup_value - E5, med stjerner (*) sammenkædet foran og bagpå
  • lookup_array - den navngivne områdekode (B5: B15)
  • return_array - det navngivne område mængde (C5: C15)
  • if_not_found - strengen "ingen match"
  • match_mode - leveres som 2 (jokertegn match)
  • search_mode - ikke angivet. Standard er 1 (første til sidste)

For at gøre en "indeholder" match match automatisk, er wildcard-stjernen (*) både forberedt og tilføjet til værdien i celle E5 med sammenkædning:

"*"&E5&"*"

Efter sammenkædning bliver formlen:

=XLOOKUP("*BCC*",code,quantity,"no match",2)

XLOOKUP lokaliserer det første match, der indeholder "BCC" (050-BCC-123 i række 10) og returnerer den tilsvarende værdi fra returarrayet, 50.

Bemærk, at XLOOKUP ikke er store og små bogstaver, hvis du indtaster "bcc" i E5, returneres det samme resultat:

=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50

Se nedenfor for en mulighed for at konfigurere XLOOKUP til en sagsfølsom match.

VLOOKUP-mulighed

VLOOKUP-formlen understøtter også jokertegn, når de er indstillet til nøjagtigt match. Den ækvivalente VLOOKUP-formel til dette eksempel er:

=VLOOKUP("*"&E5&"*",B5:C15,2,0)

Fuld forklaring her.

Med SØG og FIND

Det er også muligt at bruge funktionerne SØG og FIND til at udføre en "indeholder" -typekamp med XLOOKUP. For en sag-ufølsom match (som eksemplet ovenfor) kan du bruge SØG på denne måde:

=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)

For en sagsfølsom match kan du bruge FIND i stedet:

=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)

Begge muligheder ovenfor gør det lettere at udvide kriterierne til at omfatte andre betingelser ved hjælp af boolsk logik.

Logikken for ISNUMBER + SEARCH forklares her.

Flere kampe

Se FILTER-funktionen, hvis du har brug for flere matches.

Interessante artikler...