¿Es posible hacer referencia a dos celdas en Excel para encontrar una tercera celda?

Question

Intenté hacerlo funcionar bajo el supuesto de que:

"Componentes adjuntos" siempre está en la misma columna.
En realidad, está intentando buscar la descripción de cada "N.º de material".

Y usaré esta hoja para trabajar en:

Ejemplo

Puede que esto no sea exactamente lo que necesita, pero puedo intentar mejorar mi respuesta con sus comentarios al respecto.

Al reutilizar su fórmula para identificar dónde están los "Componentes adjuntos" en la columna y luego agregar 2, se obtiene la fila relativa donde comienza la descripción del material:

=MATCH("Attached Components",B1:B32,0)+2

El resultado está en el ejemplo "7".

Luego debe identificar la última fila donde están las descripciones. Para buscar en el rango correcto, la fórmula debe cambiar según en qué fila se encuentre "Componentes adjuntos". La combinación de COINCIDIR, DIRECCIÓN, CONCATENAR recreará el rango.

MATCH le proporciona la fila relativa, ADDRESS transforma un número de fila y un número de columna en una cadena con el nombre de la celda ( ADDRESS(1,1)="$A$1" ), CONCATENATE juntará las cadenas para crear un rango.

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

Esto devuelve una cadena como "$C$7:$C$25". Por lo tanto, cubre la columna Descripción y comienza en la fila donde tiene sus valores 18 filas más abajo. Para cubrir más o menos filas simplemente cambie "+20" en la fórmula al valor apropiado.

Encontrar la última fila es sólo cuestión de encontrar la primera celda vacía con IF y MIN.

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

Esta fórmula es una fórmula matricial. Es por eso que tiene corchetes alrededor (no escriba los corchetes, aparecen cuando ingresa la fórmula y luego presiona Ctrl+Shift+Enter)

INDIRECTO transforma la cadena que construimos en una referencia de celda. FILA da como resultado el número de fila. MIN tomará el valor más pequeño en el rango devuelto. El "-1" al final debe tener el número de fila de la última descripción y no la primera fila en blanco.

En el ejemplo, esta fórmula devuelve "9".

Ahora tenemos el número de fila de la primera descripción y la última descripción, del 7 al 9. Podemos combinar esos números de la forma que queramos usando DIRECCIÓN, CONCATENAR e INDIRECTO para realizar cualquier operación que necesite. Pero esta vez tienes una referencia de celda específica con la que trabajar.

Por ejemplo, una búsqueda de número de material:

Ejemplo de BUSCARV

En este último ejemplo, las celdas contienen

E2:

=MATCH("Attached Components",B1:B32,0)+2

F2 (Para ingresar usando Ctrl+Shift+Enter):

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

F7:

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

De esta manera, cuando escribe un número de material en la celda E7, se muestra la descripción en la celda F7.

EDITAR:

Siguiendo los comentarios, la solución se puede resolver de esa manera:

Usando un ejemplo más complicado:

Ejemplo complicado

La coincidencia de filas es solo una cascada de la función 2 MATCH. Usando la primera función COINCIDIR para encontrar el número de pieza y luego la segunda para encontrar la sección de interés:

Los 2 coincidentes

F3: una cadena de la pieza que buscas

F4: la fórmula para buscar el "N.º de pieza" en la primera columna.

=MATCH($F$3,A1:A32,0)

F6: el nombre de la sección que estás buscando

F7: la fórmula para buscar la sección en la pieza identificada anteriormente. La coincidencia se realiza en un rango que comienza en la fila del "N.º de pieza" (almacenado en la celda F4). El rango se construye usando el mismo tipo de fórmula que usa INDIRECTO, CONCATENAR, DIRECCIÓN. Luego, la fila relativa devuelta por MATCH se compensa con F4-1 para tener el número de fila absoluto.

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

Ahora, para identificar la primera y última fila de la descripción podemos reutilizar las mismas fórmulas que antes:

primera y última fila para descripciones

F9: suma 2 al número de fila de la fila "Componentes adjuntos" para obtener la primera fila de descripción.

=F7+2

F10: busca la primera fila en blanco en el rango de descripción (comenzando en la fila almacenada en F9). Esta es una fórmula matricial que debe ingresarse usandoCTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

Luego para mostrar la descripción podemos usar INDIRECTO y una columna de índice:

Matriz de visualización

F15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

G15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

Esas fórmulas mostrarán el número de material y la descripción de una fila identificada por un índice en la columna E. La declaración IF es para asegurarnos de que no mostremos las filas que están debajo de las últimas filas. En el ejemplo, muestra solo 5 filas, pero puedes copiar esta fórmula arrastrando hacia abajo la primera fila y agregando nuevos índices para tener más.

Answer 1