Usando o VLOOKUP e várias planilhas no Excel

Question

Aqui está o problema:

Estou construindo um sistema de inventário de estoque. Possui 3 folhas: Transações (onde as transações efetuadas são entradas), Recebidas (onde o novo estoque recebido é entrado) e Estoque (onde os custos e a quantidade em estoque são mostrados).

As páginas Transações e Recebidas usam listas suspensas para selecionar itens do intervalo de produtos (que são armazenados na planilha Back-end).

Na página de estoque, digamos na célula "Biscuits" Quantidade em estoque, eu gostaria de procurar quaisquer ocorrências de "Biscuits" na planilha de transações de hoje ou antes, obtendo as quantidades de cada. Depois, preciso fazer o mesmo com a planilha Received e menos os dois números para obter a quantidade real em estoque.

Eu examinei vários guias sobre o uso da função VLOOKUP com várias planilhas, no entanto, todos eles parecem retornar um valor na mesma planilha que o valor de pesquisa, em vez de em uma planilha diferente. Isso resulta em uma mensagem # N / A toda vez.

A folha pode ser encontrada aqui

https://www.dropbox.com/s/pf1wzsa7ruhjzc1/stockSystem.xlsx?dl=0

para que você possa ver o problema com mais clareza.

Se alguém pudesse me apontar na direção certa, seria apreciado.

Obrigado Luke

    

Answer 1

Como sua planilha é salva como .xlsx, acredito que você esteja usando o Excel 2007 ou superior. Basicamente, enquanto houver um erro na sua função VLOOKUP, em princípio você pode não obter os resultados desejados se usar o VLOOKUP ou o INDEX MATCH no seu caso.

O que você está tentando fazer é pesquisar um item em "Transação" & Folhas 'Recebidas' com duas condições, de acordo com os seus comentários inline.

1 - o nome do item deve corresponder

2 - A data deve ser menor ou igual à data atual

Com essas duas condições atendidas, você precisa somar a quantidade.

Aparentemente, não sei ao certo por que você precisa corresponder à data, geralmente porque não deve haver nenhuma transação futurista nela.

No entanto, isso pode ser feito usando a função SUMIFS que está disponível no Excel 2007 em diante.

Veja as capturas de tela abaixo.

Na sua célula B6 da planilha chamada Estoque, coloque a seguinte fórmula e arraste-a para baixo. Neste exemplo, peguei um pequeno intervalo de células, digamos A4 a A14, mas ele pode ser modificado de acordo com a sua planilha.

=SUMIFS(Received!$E$4:$E$14,Received!$A$4:$A$14,"<="&NOW(),Received!$D$4:$D$14,A6)-SUMIFS(Transactions!$E$4:$E$15,Transactions!$A$4:$A$15,"<="&NOW(),Transactions!$D$4:$D$15,A6)

    

Answer 2

A resposta de Rohit é boa. Particularmente a segunda opção é muito mais leve e ainda retorna o resultado desejado.

Outra opção é usar INDEX e MATCH. Dessa forma, você pode eliminar o requisito de valor de consulta da primeira coluna do VLOOKUP -

=INDEX(Received!$E$4:$E$10, MATCH(A6, Received!$D$4:$D$10, 0))

    

Answer 3

Sua fórmula VLOOKUP é = VLOOKUP (A6, Received! A4: F27,5, FALSE). No entanto, ele está procurando por 'Biscuits' na Coluna A, mas esse item está na coluna D. VLOOKUP Procura o valor na primeira coluna da tabela.

Intercambie suas colunas A & D na sua planilha 'Recebido' se isso funcionar bem com você e o VLOOKUP deve funcionar bem.

Como alternativa, altere a fórmula para = VLOOKUP (A6, Recebido! D4: F27,2, FALSE) no caso de as 3 primeiras colunas não importarem e ele pesquisar corretamente.