O Excel pode usar uma matriz “computada” para o Vlookup?

Question

Na captura de tela abaixo, qual texto substituiria o texto vermelho para permitir intervalos de G e H a serem calculados no VLOOKUP ?

Eu tentei vários usos de Endereço e concatenados, mas não consigo encontrar uma solução.

Veja a fonte dessa pergunta para obter um texto parcial (copiar e colar) versão da planilha acima.

    

Answer 1

A função que você está procurando é INDIRECT() . Isso retorna uma referência especificada por uma string. Por exemplo, INDIRECT("B2") é equivalente a uma referência à célula B2 , e INDIRECT("B2:C4") é equivalente a uma referência ao intervalo B2:C4 .

Obviamente, você precisa obter os valores de string de texto de G2:H11 em D18:D32 . Isso é facil; use apenas VLOOKUP(A18, A$2:H$11, 7) e VLOOKUP(A18, A$2:H$11, 8) . Usando esses blocos de construção, obtemos essa fórmula para D18 :

=VLOOKUP(B18, INDIRECT(VLOOKUP(A18, A$2:H$11, 7) & ":" & VLOOKUP(A18, A$2:H$11, 8)), 2)

em que combinamos o valor de “primeira célula” (da coluna G , a.k.a. coluna 7) e o valor da "última célula" (da coluna H , a.k.a. coluna 8) com dois pontos ( : ), usando o operador de concatenação de seqüência de caracteres & . Em seguida, use INDIRECT() para transformar sequências como B2:C4 em intervalos pesquisáveis. Em seguida, arraste / preencha D18 para baixo.

A% de comissão por bob aparece como #N/A porque as datas dele estão erradas: B5 e B6 são em 2017, mas B25:B27 são em 2016.

    

Answer 2

Você tem dois critérios, por isso é mais simples se você aprender a usar INDEX MATCH .

=INDEX($C$2:$C$11,
       MATCH(1, (A18 = $A$2:$A$11) * (B18 < $B$2:$B$11), 0)
 )

e lembre-se de pressionar Ctrl + Shift + Enter em vez de Inserir para inserir esta fórmula de matriz.

$C$2:$C$11 é a sua coluna da Comissão

MATCH(1, ..., 0) está apenas procurando onde as duas condições são verdadeiras.

A18 = $A$2:$A$11 está procurando por um nome correspondente

B18 < $B$2:$B$11 está procurando por uma data anterior à data de referência