☰

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

tag-icon • tag-icon microsoft-excel
Pesquisa baseada em dois critérios, um número e outro intervalo

Tenho duas listas, uma com IDs e porcentagens baseadas em vários intervalos, e outra com IDs e totais. Preciso consultar o percentual da primeira planilha com base no ID e total da 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

Responder1

Supondo que sua primeira tabela esteja na Planilha1 e sua segunda tabela esteja na Planilha2 e que você deseja preencher a coluna Valor da sua segunda tabela, a fórmula a seguir fará isso para a primeira célula Valor (célula C2) da Planilha2. Isso requer que a primeira tabela seja classificada pela coluna ID e depois pela coluna Inferior. Esta fórmula pode ser copiada e colada no final da tabela 2 após ser 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.

Decompondo esta fórmula, ele usa oVLOOKUPfunção 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 às linhas que correspondem ao ID correto, e faz isso com oOFFSETfunção. OOFFSETfunção usa a linha do primeiro ID correspondente encontrado com oMATCHfunção e a contagem total de IDS correspondentes encontrados com oCOUNTIFfunção.

informação relacionada