¿ Es posible en Excel hacer una búsqueda como ésta?
Para encontrar el ID y la Línea de cada producto he utilizado la función BuscarV con los datos de la matriz 2. Pero me pregunto si es posible utilizar los datos de la matriz 1 para buscar la Línea de producto.
3 Respuestas
Pon la siguiente fórmula en la celda F3
=DESREF($A$2,0,SUMAPRODUCTO(($H$3:$I$40=D3)*(COLUMNA($H$2:$I$2)))-1)
Si aumentas las Líneas de producto en la matriz 1, por ejemplo:
Entonces en la fórmula tienes que cambiar la columna "I" por la columna "J"
=DESREF($A$2,0,SUMAPRODUCTO(($H$3:$J$40=D7)*(COLUMNA($H$2:$J$2)))-1)
.
'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias
.
Avísame cualquier duda
.
Saludos Dante. Funciona bien. Pero soy novato en Excel y no conocía la fórmula =DESREF. Le doy mil gracias si me explica con más detalle, para entender cómo se relacionan todas las fórmulas que utilizó.
Te propongo dos maneras:
1) La matriz 1 tiene 2 filas de datos (sin contar encabezados)... si tiene pocas, entonces se puede usar una fórmula como esta... que veras es más grande conforme existen más filas
para 2 filas...
=INDICE($H$2:$I$2,SI.ERROR(COINCIDIR($D3,$H$3:$I$3,0),COINCIDIR($D3,$H$4:$I$4,0)))
para 3 filas...
=INDICE($H$2:$I$2, SI.ERROR(SI.ERROR(COINCIDIR($D3,$H$3:$I$3,0),COINCIDIR($D3,$H$4:$I$4,0)),COINCIDIR($D3,$H$5:$I$5,0)))
No me parecería nada práctica para más filas...
2) La otra manera... con una fórmula matricial
=INDICE($H$2:$I$2,MIN(SI($D3=$H$3:$I$4,COLUMNA($H$3:$I$4)-COLUMNA($G$2),"")))
que se debe introducir con Ctrl+Shift+Enter. Para trabajar con una tabla más grande simplemente cambia el "4" por la verdadera fila donde termine tu tabla.
En mi Excel los argumentos se separan con, cambialos por ; para el tuyo
Saludos,
Jaime
PD: No olvides valorar la respuesta
Hola Jaime
Tengo varias dudas.
- Sobre la combinación Ctrl+Shift+Enter y su relación con la fórmula.
- La fórmula INDICE requiere de tres parámetros: matriz, fila y columna. No me queda claro cuál parte de la función cumple el papel de columna.
- Cuando saco la fórmula condicional =+SI($D3=$H$3:$I$4;COLUMNA($H$3:$I$4)-COLUMNA($G$2);"-") y la copio a otra celda para ver el resultado me da error de valor.
- ¿ Cuál es el papel de la función mínimo?
Te respondo:
1.
Una fórmula matricial se produce cuando en una función y operación en la que estaba involucrada una celda, o un único valor, se introduce un vector o matriz (un rango con varios elementos)
Por ejemplo:
COLUMNA(G3) da como respuesta 7 pues la G es la 7ma columna
pero COLUMNA(G3:J3) da como respuesta { 7 , 8 , 9 , 10} pues evalua para G, H, I y J
Si usas ese valor en alguna operación como SUMA(COLUMNA(G3:J3)).. Excel no puede sacar la suma que para uno lógicamente es 7+8+9+10... a menos que la introduzcas con Ctrl+Shift+Enter..
Así Excel se da cuenta que es una fórmula matricial... y cualquier modificación futura a la celda, hay que seguir re-introduciendola con esa combinación de teclas.
En el SI($D3=$H$3:$I$4 tambien estas comparando una celda contra todo una matriz de celdas... eso tambien convierte a la fórmula en matricial
2.
La función INDICE tiene 2 sintaxis: MATRICIAL y REFERENCIA
Pero la sintaxis MATRICIAL tiene a su vez dos maneras...
MATRICIAL propiamente dicha, que es el ejemplo que mencionas y la
VECTORIAL, en donde el rango es una simple fila o simple columna... es ese caso solo necesita el número de elemento para calcular lo que quieres...
En este caso eso es lo que estoy haciendo...
La función si, se encarga de calcular mediante la diferencia del numero de columna del rango H3:I4 con la celda G2, un numero que va de 1 a 2, que es a su vez el numero de posición en el vector INDICE H2:I2 en donde se encuentra el valor que necesitas.
Te recomiendo armar la fórmula y luego seleccionando con el mouse los argumentos internos verificar el valor de ellos presionando F9... allí verás que es lo que produce como resultado cada parte de la fórmula.
3.
Una fórmula matricial como el SI que te propongo genera un conjunto de respuesta... no una respuesta única... Eso Excel no es capaz de mostrarla en una sola celda. Necesita una función de "Valor agregado"... que es una función que con varios valores genera un único valor de respuesta... Ejemplo: SUMA, CONTAR, MIN, MAX, PROMEDIO... etc... recién allí, y con el uso de Ctrl+Shift+Enter se calcula un único resultado que Excel si es capaz de mostrar en una celda.
4.
La función de MIN es justamente consolidar los resultado de SI...
SI genera una matriz, lleva de valores vacíos y un único resultado numérico que es en el fondo la ubicación en el rango de INDICE...
Pero se necesita un valor como por ejemplo 2... que para sacarlo de una matriz como
{"", 2 ; "", ""} tiene que pasar por una función de valor agregado...
En general si tu sabes que tienes un vector o matriz lleno de "" (que en el fondo son texto) y un único valor numérico, podrías haber usado casi cualquier función de valor agregado, SUMA, MAX, MIN, PROMEDIO... pues el resultado indefectiblemente hubiera sido justo ese número...
Yo use MIN... que en el peor de los casos, si hay más de un valor coincidente, te saca aquel que está, más a la izquierda.
Espero que esté más claro,
Saludos,
Jaime
PD: No olvides valorar la respuesta
Gracias Jaime,
Ya comprendo mejor.
Según entiendo para la matriz indicada para la función INDICE, cuyo valores varían de 1 a 2, se determina la posición de cada celda referenciada en la función SI.
La función SI combinada con MIN precisamente nos permite determinar si el valor mínimo de la celda referenciada es 1 o 2 para este caso en específico.
Esto te puede aportar algo más