Como procurar um valor em uma tabela com vários critérios

Question

Eu tenho uma folha de dados com vários valores em várias colunas. Eu tenho um qty e um preço atual que quando multiplicado me dá a receita atual (CurRev).

Eu quero usar essa tabela de pesquisa para me fornecer a nova receita (NewRev) do novo preço, mas não consigo descobrir como fazer vários ifs em uma pesquisa.

Oqueeuqueroécriarumanovacolunaqueverifiqueo"Produto", "Camada" e "Localização / Estado" e me dê o novo preço na tabela de pesquisa (acima) e multiplique isso pelo qtd. / p>

por exemplo. Dados

> Product,  Tier,  Location, Qty, CurRev,   NewRev 
> Product1, Tier1, VIC,      2,   $1000.00, $6000 (2 x $3000) 
> Product2, Tier3, NSW,      1,   $100.00,  $200 (1 x $200)
> Product1, Tier3, SA,       5,   $250.00,  $750 (5 x $150)
> Product3, Tier1, ACT,      5,   $100.00,  $500(5 x $100)
> Product2, Tier3, QLD,      2,   $150.00,  $240 (2 x $240)

Na pior das hipóteses, se eu pegar a nova taxa, posso criar outra coluna

    

Answer 1

Esta solução faz duas grandes suposições sobre seus dados: 1) que cada produto tem exatamente três camadas; e 2) os IDs do produto na tabela de consulta estão em células mescladas. Por favor, deixe-me saber se este não é o caso.

Aqui está uma fórmula que fornecerá o valor NewRev para um registro na linha 18.

=VLOOKUP(B18,INDIRECT("B"&MATCH(A18,$A$1:$A$10)&":J"&MATCH(A18,$A$1:$A$10)+2),MATCH(C18,$A$1:$J$1,0)-1,FALSE)*D18

Esta fórmula usa a função INDIRECT para definir qual parte da tabela de consulta deve ser usada para seu VLOOKUP com base no ID do produto. O número da coluna para o VLOOKUP é obtido usando uma função MATCH para encontrar o local apropriado.

    

Answer 2

Acho que seria mais fácil se a coluna A mostrasse o produto relevante em todas as linhas. Se você fizer isso, você pode usar esta fórmula

=INDEX(C$2:J$10,MATCH(1,INDEX((A$2:A$10=A18)*(B$2:B$10=B18),0),0),MATCH(C18,C$1:J$1,0))*D18

.... que segue uma configuração padrão INDEX / MATCH / MATCH para obter dados da interseção de um cabeçalho de coluna e linha (um pouco complicado aqui ao ter que combinar dois critérios para obter a linha)