Pesquisa baseada em dois critérios, um um número e um um intervalo

Question

Eu tenho duas listas, uma com IDs e porcentagens baseadas em vários intervalos e a outra com IDs e totais. Preciso procurar o percentual na primeira planilha com base no ID e no total na segunda planilha.

ID  Bottom Top  Percentage
1   1      50       0.3
1   51     75       0.4
1   76              0.5
2   1               0.75
3   1      25       1
3   26     100      2
3   101    1000     3
3   1001   2000     4
3   2001            5

Second List
ID  Total Amount
1   100
2   146
3   1256

    

Answer 1

Supondo que sua primeira tabela esteja na Planilha1 e sua segunda tabela esteja na Planilha2 e deseje preencher a coluna Montante da segunda tabela, a fórmula a seguir fará isso para a primeira célula Quantidade (célula C2) da Planilha2. Isso requer que a primeira tabela seja classificada pela coluna ID e, em seguida, pela coluna Inferior. Essa fórmula pode ser copiada até o final da tabela 2 depois de inserida.

=VLOOKUP(B2, OFFSET(Sheet1!B:D, MATCH(A2, Sheet1!A:A, 0) - 1, 0, COUNTIF(Sheet1!A:A, A2)), 3)

Observe que a coluna Superior não é realmente necessária para que isso funcione.

Quebrando essa fórmula, ela usa o VLOOKUP para pesquisar na coluna Inferior o valor mais próximo menor ou igual ao valor Total e, em seguida, retorna o valor na coluna Porcentagem para a mesma linha. Ele também precisa limitar a pesquisa a linhas que correspondam ao ID correto, e isso é feito com o OFFSET função. A função OFFSET usa a linha do primeiro ID correspondente encontrado com a função MATCH e a contagem total de IDS correspondentes encontrados com o COUNTIF função.