He estado trabajando en un problema de Excel durante la mayor parte de las 24 horas. Tengo una fórmula que casi hace lo que quiero pero no devuelve lo que imagino que debería devolver:
=INDEX(Data!D:D,SUMPRODUCT((Data!C:C=Total!A5)*(Data!A:A="Total")*(Data!B:B="Total")*(ROW(Data!D:D))))
¡Me imagino la fila para indexar la columna D en los Datos! hoja de cálculo es la fila real en la que se produce la coincidencia, pero PARA CADA VALOR MENOS EL PRIMER VALOR, el valor devuelto proviene de 3 filas debajo de él.
Sí, entonces para la primera celda en la que aplico esa fórmula, obtengo el valor correcto de la columna D de datos. Pero, para cada celda posterior con la fórmula, obtengo el valor en la Columna D de Datos. 3 filas debajo del fósforo.
Probé todas las combinaciones para restar 3 de la fórmula (para devolver la fila correcta), pero fue en vano. Aún así, eso no explicaría por qué la primera celda en la que se aplica la fórmula devuelve el valor esperado.
Cualquier ayuda es muy apreciada, por supuesto...
Respuesta1
No me queda del todo claro si quieres encontrar elprimero, segundo, etc.coincide con Total!A5 o si está intentando encontrar la primera coincidencia con A5, entonces la primera coincidencia con A6, etc. Esta solución propuesta es para la primera.
Prefiero utilizar el método de exclusión matemática para detectar múltiples resultados en partidos. Si elimina lo que no coincide, lo que queda debe ser el conjunto de resultados coincidentes. También estoy lo suficientemente impaciente como para encontrar SUMPRODUCTmolesto el retraso en el cálculo de las referencias de columnas completas, por lo que reduje las referencias de rango a las primeras ~ 1K filas.
=IFERROR(INDEX(Data!$D$2:$D$999, SMALL(INDEX(ROW($1:$998)+((Data!$C$2:$C$999<>Total!$A$5)+(Data!$A$2:$A$999<>"Total")+(Data!$B$2:$B$999<>"Total"))*1E+99,,),ROW(1:1))),"")
Este es un estándar (sin matriz) fórmula. Complete según sea necesario para capturar todas las devoluciones coincidentes. Cuando transcribas esto para tus propios fines, recuerda que esa ROW(1:998)es laposición dentro Data!D2:D999, no el número de fila real en la hoja de trabajo. ROW(1:1)es simplemente un contador que aumenta a medida que se llena. A veces a COUNTIFes más apropiado allí y puede usarse para capturar múltiples resultados coincidentes en múltiples valores de búsqueda.