Fórmula genérica
=XLOOKUP("*"&value&"*",lookup,results,,2)
Resumen
Para usar XLOOKUP para hacer coincidir valores que contienen texto específico, puede usar comodines y concatenación. En el ejemplo que se muestra, la fórmula en F5 es:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
donde el código (B5: B15) y la cantidad (C5: C15) son rangos con nombre.
Explicación
La función XLOOKUP contiene soporte integrado para comodines, pero esta función debe habilitarse explícitamente configurando el modo de coincidencia en el número 2.
En el ejemplo que se muestra, XLOOKUP está configurado para coincidir con el valor ingresado en la celda E5, que puede aparecer en cualquier lugar de los valores de búsqueda en B5: B15. La fórmula en F5 es:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, con asteriscos (*) concatenados al frente y al dorso
- lookup_array - el código de rango con nombre (B5: B15)
- return_array - la cantidad de rango con nombre (C5: C15)
- if_not_found - la cadena "no coincide"
- match_mode: proporcionado como 2 (coincidencia de comodín)
- search_mode - no proporcionado. El valor predeterminado es 1 (del primero al último)
Para que una coincidencia de tipo "contiene" sea automática, el asterisco comodín (*) se antepone y se agrega al valor en la celda E5 con concatenación:
"*"&E5&"*"
Después de la concatenación, la fórmula se convierte en:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP localiza la primera coincidencia que contiene "BCC" (050-BCC-123 en la fila 10) y devuelve el valor correspondiente de la matriz de retorno, 50.
Tenga en cuenta que XLOOKUP no distingue entre mayúsculas y minúsculas, al ingresar "bcc" en E5 se obtendrá el mismo resultado:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Vea a continuación una opción para configurar XLOOKUP para una coincidencia sensible a mayúsculas y minúsculas.
Opción VLOOKUP
La fórmula VLOOKUP también admite comodines cuando se configura para una coincidencia exacta. La fórmula VLOOKUP equivalente para este ejemplo es:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Explicación completa aquí.
Con BUSCAR y ENCONTRAR
También es posible utilizar las funciones BUSCAR y ENCONTRAR para realizar una coincidencia de tipo "contiene" con XLOOKUP. Para una coincidencia que no distingue entre mayúsculas y minúsculas (como el ejemplo anterior), puede usar SEARCH de esta manera:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Para una coincidencia que distingue entre mayúsculas y minúsculas, puede utilizar FIND en su lugar:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Ambas opciones anteriores facilitan la ampliación de criterios para incluir otras condiciones utilizando lógica booleana.
La lógica para ISNUMBER + SEARCH se explica aquí.
Partidos múltiples
Si necesita varias coincidencias, consulte la función FILTRO.
