Vlookup com mesas de sol

Question

Preciso calcular "pontos" para cada linha com base nas Categoria e Listagens

eeupossobuscardadosdestatabelaabaixo.

Porexemplo,aprimeiralinhaé:

Categoria:Hardlines

Anúncios:165

oresultadoparaascolunas"pontos" deve ser - > Pontos: 2

Você sabe como fazer isso no excel? Eu não posso alterar a estrutura da tabela de origem

    

Answer 1

Use uma fórmula como esta:

=INDEX(B:D,MATCH(H2,A:A,0)+2,MATCH(G2,INDEX(--(LEFT(INDEX(B:D,MATCH(H2,A:A,0)+1,0),FIND("-",INDEX(B:D,MATCH(H2,A:A,0)+1,0))-1)),)))

    

Answer 2

Primeiro, normalize sua tabela de referência. É mais fácil fazer isso quando você não precisa fazer referência ao intervalo de valores (1-19).

Hardlines   Tier 0  | 150   | 2
Hardlines   Tier 1  | 20    | 1
Hardlines   Tier 2  | 1     | 0.5
Softlines   Tier 0  | 100   | 2
Softlines   Tier 1  | 20    | 1
Softlines   Tier 2  | 1     | 0.5
Media       Tier 0  | 500   | 2
Media       Tier 1  | 20    | 1
Media       Tier 2  | 1     | 0.5

Tenha em atenção que os intervalos de valor estão ordenados DESCENDING (150 > 20 > 1)

Depois disso, insira esta fórmula na sua coluna Pontos.

=INDEX($N$1:$N$9, MATCH(1, (B1 >= $M$1:$M$9) * (C1 = $K$1:$K$9), 0))

e pressione Ctrl + Shift + Enter em vez de Digite para inserir a fórmula da matriz.

A fórmula está procurando Points referenciada na coluna N, que corresponde a dois critérios:

1. B1 >= $M$1:$M$9 O valor na pesquisa é maior que os valores referenciados na coluna M.

2. C1 = $K$1:$K$9 O valor na pesquisa corresponde à string referenciada na coluna K.