Fórmula genérica
(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))
Resumen
Para recuperar múltiples valores coincidentes de un conjunto de datos con una fórmula, puede usar las funciones SI y PEQUEÑO para calcular el número de fila de cada coincidencia y retroalimentar ese valor al ÍNDICE. En el ejemplo que se muestra, la fórmula en I7 es:
(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))
Donde los rangos con nombre son amts (D4: D11), id (I3) e ids (C4: C11).
Tenga en cuenta que esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.
Explicación
En esencia, esta fórmula es simplemente una fórmula de ÍNDICE que recupera el valor de una matriz en una posición determinada. El valor de n se proporciona en la columna H, y todo el trabajo "pesado" que hace la fórmula es averiguar la fila de la que recuperar un valor, donde la fila corresponde a la coincidencia "n-ésima".
La función SI hace el trabajo de averiguar qué filas contienen una coincidencia, y la función PEQUEÑA devuelve el enésimo valor de esa lista. Dentro de IF, la prueba lógica es:
ids=id
que produce esta matriz:
(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Tenga en cuenta que la identificación del cliente coincide en las posiciones 1 y 4, que aparecen como VERDADERO. El argumento "valor si es verdadero" en IF genera una lista de números de fila relativos con esta expresión:
ROW(ids)-ROW(INDEX(ids,1,1))+1
que produce esta matriz:
(1;2;3;4;5;6;7)
Luego, esta matriz se "filtra" por los resultados de la prueba lógica y la función SI devuelve el siguiente resultado de la matriz:
(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)
Tenga en cuenta que tenemos números de fila válidos para la fila 1 y la fila 2.
Esta matriz luego es procesada por SMALL, que está configurado para usar valores en la columna H para devolver valores "n-ésimo". La función PEQUEÑO ignora automáticamente los valores lógicos VERDADERO y FALSO en la matriz. Al final, las fórmulas se reducen a:
=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125
Manejo de errores
Una vez que no haya más coincidencias para una identificación determinada, la función PEQUEÑA devolverá un error #NUM. Puede manejar este error con la función IFERROR o agregando lógica para contar coincidencias y cancelar el procesamiento una vez que el número en la columna H sea mayor que el recuento de coincidencias. El ejemplo aquí muestra un enfoque.
Varios criterios
Para agregar varios criterios, use la lógica booleana, como se explica en este ejemplo.
