En Excel necesito encontrar datos en la hoja 3 a partir de datos en la hoja 1

Question

Tengo un libro de Excel con varias hojas. La columna 'SheetA'!W:Wenumera el texto que se puede encontrar dentro del encabezado de una columna en la fila 'Sheet3'!1:1. Por ejemplo, SheetA'!W42contiene "B16". La celda Sheet3'!CB1tiene el texto de encabezado "B16: Muestra 40", por lo que sería la columna de destino.

Ejemplo: muestra de'SheetA'!W42

Hay 'SheetA'!CD42una fórmula que calcula el número de muestra que debe ubicarse 'Sheet3'!A:A. En el siguiente ejemplo, CD42se muestra el valor calculado "30", que se puede encontrar en 'Sheet3'!A32.

Ejemplo: datos de muestra de la Hoja 3 que muestran los encabezados y la intersección del encabezado de la columna (B16) y la fila que contiene el número de muestra de interés:

El objetivo es recuperar el contenido de la celda en la Hoja3 donde se cruzan la columna y la fila buscadas y mostrarlo en una celda en la HojaA. En el ejemplo proporcionado, en 'Sheet3'!CB:CBlas intersecciones de la fila 32 y en la hoja A se mostraría el valor "0,1950581843".

¿Alguien sabe cómo se puede lograr esto?

Answer 1

Teniendo en cuenta los datos anteriores, haré algunas suposiciones. Si todo esto es cierto, entonces HLOOKUPpodemos resolver fácilmente su problema. De lo contrario, es posible que se necesite una cadena de funciones más compleja.

Supuestos:

1. Todos los textos de encabezado relevantes en 'Sheet3'!1:1sonabsolutamente únicodentro de esa fila.
2. Todos los textos de encabezado relevantes 'Sheet3'!1:1tienen el formato "[X]: [Y]", donde:
   • [X] es un valor que se puede encontrar en 'SheetA'!W:W.
   • [Y] es el valor 'SheetA'!X:Xque está en la misma fila que [X].
3. Todos los valores posibles en 'SheetA'!CD:CDse pueden buscar en 'Sheet3'!A:A, de modo que estarán en la misma fila que los datos que se pretende recuperar.
   • Solo estoy poniendo esto porque noté que parece haber cierta repetición de estos valores en la Hoja 3 y quería asegurarme de que no hubiera condiciones bajo las cuales se pudiera buscar un valor 'SheetA'!CD:CDque debiera compararse con otra columna. en su lugar (y por lo tanto puede producir resultados inexactos si coinciden 'Sheet3'!A:A).
   • Si dicha repetición es estrictamente para facilitar la lectura, ¿podría sugerir el uso de la función "Congelar paneles"?
4. Todos los valores relevantes en 'Sheet3!A:A'son perfectamente secuenciales, comenzando con "1" en 'Sheet3'!A3, nunca se repiten dentro de esa columna, ysiempreordenados de forma ascendente.

Dadas las suposiciones anteriores, construí mis propias hojas con datos de muestra que representan aproximadamente su escenario.

Aquí hay una captura de pantalla parcial de mi "Hoja3".

Y una toma parcial de mi "SheetA".

Lista de análogos:

• Hoja3
  • Mi A:A = Tu A:A, BY:BY:, CA:CA:, ...
  • Mi B:B, C:C, D:D, ... = Tu B:B, BZ:BZ, CB:CB, ...
• HojaA
  • Mi A:A = Tu W:W
  • Mi B:B = Tu X:X
  • Mi C:C = Tu CD:CD
  • Mi D:D = Cualquier columna en la que desee colocar los datos encontrados.

Como puedes ver en la segunda captura de pantalla, la fórmula para D2 es:

=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)

Tutorial paso a paso de la fórmula:

BUSCARHle permite buscar un valor horizontalmente a través de un rango de celdas y luego devolver el valor de una celda en la misma columna según una posición relativa de la fila. Se necesitan cuatro argumentos, tres de los cuales son obligatorios:valor de búsqueda,matriz de tabla,número_índice_fila,[rango de búsqueda]. Esta es la fórmula que hará el trabajo preliminar de encontrar los datos que desea en la Hoja3 y colocarlos en una celda de la HojaA.

• valor de búsquedaes el valor que desea HLOOKUPencontrar primero. Este valordebeestar presente en la primera fila dematriz de tabla, porque esa es la única fila en la que se buscará. HLOOKUPAdemás, solo devolverá la primera coincidencia, por lo que estos valores también deben ser únicos. Aquí, estamos usando CONCATENATEpara construir nuestra cadena de búsqueda.
  
  • CONCATENARle permite juntar varias cadenas y valores en una sola cadena. Acepta una serie de argumentos, ordenados según la secuencia en la que deben colocarse en la cadena resultante.
    
    • A2es nuestro primer argumento a favor CONCATENATE. La primera parte de nuestra cadena será el "Nombre de la muestra" en A2.
    • ": "es nuestro segundo argumento para CONCATENATE. Esto coloca los dos puntos y el espacio en la cadena, para que coincida con el formato de los valores en 'Sheet3'!1:1.
    • B2es nuestro último argumento a favor CONCATENATE. Extrae el "ID de muestra" de B2 para completar la sintaxis utilizada para los encabezados en 'Sheet3'!1:1.
• matriz de tablaes una referencia a un rango de celdas HLOOKUPcon las que desea trabajar. Recuerda que la primera fila debe ser la que contendrávalor de búsqueda. Este rango también debe abarcar todos los valores posibles paranúmero_índice_fila.
  • Hoja3!A:Yes una referencia a todas las celdas de las columnas A a la Y (las únicas llenas en mi Hoja3), de la Hoja3. Esto garantiza que cualquier dato agregado en nuevas filas más adelante también estará dentro del alcance de la búsqueda. Si los datos se fueran a agregar en nuevas columnas en lugar de filas, me gustaría usar la referencia Sheet3!1:32(actualmente, 32 es la última fila completa en mi Hoja3). Si se pueden agregar datos mediante nuevas columnasyfilas nuevas, haría referencia a la hoja completa con Sheet3!1:1048576o Sheet3!A:XFD. (Nota: la referencia a "hoja completa" es válida para Excel 2013. Las versiones anteriores pueden tener limitaciones de filas/columnas más pequeñas; ajústelas 1048576o XFDsegún corresponda).
• número_índice_filaes un número entero positivo que representa una posición de fila relativa a la fila superior enmatriz de tabla. Esto le indica HLOOKUPqué celda desea que se devuelva de la columna coincidente. Tenga en cuenta que, debido a que HLOOKUPsólo buscavalor de búsquedaen la fila superior dematriz de tabla, ynúmero_índice_filano puede ser negativo, no puede usarlo HLOOKUP(al menos, no por sí solo) para devolver información de las celdas que están arribavalor de búsqueda.
  • C2+2- Dado que todos los valores 'Sheet3'!A:Ason perfectamente secuenciales, sin números enteros omitidos, y siempre estarán ordenados de forma ascendente, podemos usar esos valores (también representados en 'SheetA'!C:C) como indicadores de los números de fila de los datos que queremos encontrar. Está +2ahí para tener en cuenta el hecho de que la numeración comienza con "1" en la fila 3 de la Hoja 3.
• [rango de búsqueda]es un argumento opcional para HLOOKUP. Las opciones son VERDADERO o FALSO, para indicar si desea permitir que las coincidencias aproximadas sean válidas o si solo se deben permitir coincidencias exactas. El valor predeterminado de Excel es VERDADERO (coincidencia aproximada) si se omite este argumento, lo que a menudo puede provocar un comportamiento no deseado, especialmente si la hoja no está ordenada de determinadas maneras. Entonces, especificamosFALSOaquí para asegurarnos HLOOKUPde que solo se seleccione una coincidencia exacta.

Adaptando lo anterior al diseño de su hoja, creo que esta es la fórmula que necesitará para la celda 'SheetA'!CE42(suponiendo que es donde desea colocar los datos):

=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)

Tenga en cuenta que, si sus datos en la Hoja 3 van más a la derecha que la columna CB, y/o se pueden agregar datos a más columnas, querrá ajustarmatriz de tablarespectivamente.