Estoy intentando tomar una lista de productos y extraer su fabricante desde el principio del nombre del producto. El nombre de cada producto comienza con su fabricante. Esto se complica por el hecho de que algunos artículos tienen otros fabricantes en el cuerpo del nombre; Necesito ver con qué comienza el artículo. Trato con más de 50.000 artículos y más de 3.000 fabricantes. La fórmula que tengo hasta ahora es:
=LOOKUP(1,1/(FIND($C$2:$C$5,B2)),$C$2:$C$5)
Esto funciona algunas veces pero no otras. Por ejemplo, en la siguiente hoja de trabajo, las filas 2 y 3 son correctas, pero la fila 4 no. El resultado en la celda A4de "Mike's Fun Toys" (en la celda B4) debería ser "Mike's", pero aparece como "Divertido".
(Aquí están los datos en forma de texto que puedes copiar y pegar:
+---+---------+-----------------+---------------+
| | A | B | C |
+---+---------+-----------------+---------------+
| 1 | Formula | Items | Manufacturers |
+---+---------+-----------------+---------------+
| 2 | Brown | Brown Cat Toys | Mike's |
| 3 | Cat | Cat Fun Toys | Fun |
| 4 | Fun | Mike's Fun Toys | Cat |
| 5 | | | Brown |
+---+---------+-----------------+---------------+
)
Pero cuando cambio el orden de la columna C(fabricantes):
la fila 4 se vuelve correcta (“Mike’s”), pero la fila 2 sale mal.
La columna A tiene la fórmula hasta el final. Resultados previstos:
A2 - Brown
A3 - Cat
A4 - Mike's
¿Cómo puedo hacer que la fórmula funcione independientemente del orden de la columna C?
Respuesta1
Para beneficio de los lectores que no entienden la fórmula de Ryan Mark, es
- Encontrar la posición de cada uno de los nombres de los fabricantes en un nombre de producto determinado. Esto resulta en
- un valor de
1para el nombre del fabricante que comienza con el nombre del producto (porque aparece en la1primer carácter), - Números más altos para los nombres de otros fabricantes que aparecen en el nombre del producto (porque aparecen más tarde, en números de posición más altos), y
- un
#VALUE!código de error para los nombres de los fabricantes que no aparecen en el nombre del producto.
- un valor de
- Invirtiendo cada uno de los anteriores (dividiendo 1 por él), dando como resultado
1por el nombre del fabricante que comienza el nombre del producto (que es el que queremos encontrar),- números positivos más bajos para los nombres de otros fabricantes que aparecen en el nombre del producto (porque 1 dividido por un número mayor que 1 produce una proporción menor que 1), y
- un
#VALUE!código de error para los nombres de los fabricantes que no aparecen en el nombre del producto.
- Usando
LOOKUPpara encontrar el1en lo anterior.
Por ejemplo, para la celda A4(correspondiente a “Mike's Fun Toys” en la celda B4), en la primera imagen obtenemos, en orden,
1, porque “Mike's” (C2) comienza con “Mike's Fun Toys”,8, porque “Fun” (C3) aparece en el octavo personaje de “Mike's Fun Toys”, y#VALUE!y#VALUE!porque “Gato” (C4) y “Brown” (C5) no aparecen en “Mike's Fun Toys”.
Invertir eso da como resultado 1, 0.125( 1/8) #VALUE!y #VALUE!. Luego busca 1en esa matriz. Este "debería" funcionar, porque 1es el primer resultado y "Mike" es el primer nombre en la columna C.
El problema se puede ver en la página de ayuda de LOOKUP:
Para elBUSCARPara que esta función funcione correctamente, los datos que se buscan deben ordenarse en orden ascendente.
y claramente 1seguido de 0.125no está ordenado en orden ascendente.
Como LOOKUPsugiere, podemos resolver esto usando MATCH. La fórmula que desea, que utiliza el mismo enfoque básico que su fórmula (excepto sin la inversión, que es innecesaria), es
=INDEX($C$2:$C$5, MATCH(1, FIND($C$2:$C$5,$B2), 0))
El tercer argumento MATCHse llama "match_type". Lo configuré 0aquí, lo que significa que MATCHbuscará el primer elemento de la matriz que sea exactamente 1, y no asumirá que la matriz está ordenada.
Esta es una fórmula matricial, por lo que debes presionar Ctrl+ Shift+ Enter cuando la ingresas.