VLOOKUP () para uma tabela em uma planilha diferente retornando um erro # N / d

Question

Eu tenho 2 tabelas, entrada diária

eresumo

MinhafórmulaemBQ20databeladeresumoestáatualmente:

=IF(VLOOKUP(BQ5,Table11[DATE],1,FALSE),VLOOKUP('SUMMARY(NEW)'!B20,'DAILYENTRY'!A:H,8,FALSE),"")

Table11[DATE] é apenas a coluna A na tabela de entrada diária.

Estou tentando coletar os valores COST e QTY da tabela de entrada diária para a tabela de resumo. No entanto, quando eu uso a fórmula acima, recebo um erro #N/A .

Estou tentando fazer isso funcionar por uma semana agora. Estou usando a fórmula errada?

    

Answer 1

VLOOKUP() funciona procurando seu primeiro argumento na coluna primeiro do intervalo especificado no segundo argumento.

A primeira coluna do seu segundo argumento deve ser a coluna ITEM , não a primeira coluna da tabela.

A fórmula corrigida para BQ20 é:

=IF(VLOOKUP(BQ5,Table11[DATE],1,FALSE),VLOOKUP('SUMMARY (NEW)'!B20,Table11[[ITEM]:[COST]],5,FALSE),"")

No entanto, esta fórmula ainda é fundamentalmente falha. Não não selecionará o item correto com base na data, pois não haverá um link entre os dois VLOOKUP() . (A tentativa de fazer isso é o que levou ao erro #N/A .)

Uma solução para isso usa matrizes e a função SUMPRODUCT() :

=SUMPRODUCT(Table11[COST]*(Table11[DATE]=$BQ$5)*(Table11[ITEM]=$B20))

Advertências:

• Esta fórmula simples só funciona corretamente se os valores que precisam ser pesquisados são números.

• Ele só funciona corretamente se os itens forem exclusivos para cada data.

Essas duas limitações podem ser superadas com versões mais complexas da fórmula.

EDITAR:

Como o OP descobriu, existe uma fórmula equivalente à acima, que usa SUMIFS() em vez de SUMPRODUCT() :

=SUMIFS(Table11[COST],Table11[DATE],$BQ$5,Table11[ITEM],$B20)

As mesmas advertências também se aplicam a essa fórmula.

Existe uma solução alternativa que faz usar VLOOKUP() , mas requer uma coluna auxiliar.

Adicione uma coluna auxiliar à tabela de entradas diárias:

InsiraaseguintefórmulaemtodasascélulasdacolunaHelper:

=Table11[[#This Row],[DATE]]&Table11[[#This Row],[ITEM]]

Digite a seguinte fórmula em BQ20 :

=VLOOKUP($BQ$5&$B20,Table11[[Helper]:[COST]],9,FALSE)

Observe que essa fórmula funciona corretamente com valores não numéricos e itens não únicos, como é.