Fórmula genérica
(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))
Resumo
Para recuperar vários valores correspondentes de um conjunto de dados com uma fórmula, você pode usar as funções IF e SMALL para descobrir o número da linha de cada correspondência e alimentar esse valor de volta para INDEX. No exemplo mostrado, a fórmula em I7 é:
(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))
Onde os intervalos nomeados são amts (D4: D11), id (I3) e ids (C4: C11).
Observe que esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter.
Explicação
Basicamente, essa fórmula é simplesmente uma fórmula INDEX que recupera o valor em uma matriz em uma determinada posição. O valor para n é fornecido na coluna H, e todo o trabalho "pesado" que a fórmula faz é descobrir a linha da qual recuperar um valor, onde linha corresponde à "enésima" correspondência.
A função IF faz o trabalho de descobrir quais linhas contêm uma correspondência, e a função SMALL retorna o enésimo valor dessa lista. Dentro de IF, o teste lógico é:
ids=id
que produz esta matriz:
(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Observe que o ID do cliente corresponde na 1ª e 4ª posições, que aparecem como TRUE. O argumento "valor se verdadeiro" em IF gera uma lista de números de linhas relativos com esta expressão:
ROW(ids)-ROW(INDEX(ids,1,1))+1
que produz esta matriz:
(1;2;3;4;5;6;7)
Essa matriz é então "filtrada" pelos resultados do teste lógico e a função IF retorna o seguinte resultado da matriz:
(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)
Observe que temos números de linha válidos para as linhas 1 e 2.
Esta matriz é então processada por SMALL, que é configurado para usar valores na coluna H para retornar valores "enésimos". A função SMALL ignora automaticamente os valores lógicos TRUE e FALSE no array. No final, as fórmulas se reduzem a:
=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125
Tratamento de erros
Quando não houver mais correspondências para um determinado id, a função SMALL retornará um erro #NUM. Você pode lidar com esse erro com a função IFERROR ou adicionando lógica para contar correspondências e interromper o processamento quando o número na coluna H for maior que a contagem de correspondências. O exemplo aqui mostra uma abordagem.
Critérios múltiplos
Para adicionar vários critérios, você usa a lógica booleana, conforme explicado neste exemplo.
