MS Excel vlookup retorna apenas uma única instância

Question

Eu tenho uma folha como

       A      |       B     |     C   |
1  Customer 1 |  Product 1  |  $150   |
1  Customer 1 |  Product 1  |  ----   |
2  Customer 1 |  Product 2  |  $50    |
3  Customer 2 |  Product 1  |  $150   |

A ideia é que a coluna C: C extrai o preço de um vlookup, mas não quero adicionar o preço se o mesmo produto já tiver sido usado para esse cliente , eu tenho essa fórmula que funciona quase, mas elimina para o mesmo cliente mesmo com um produto diferente. A fórmula é para a coluna C.

=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="","",IF(COUNTIF($A$3:A3,B3)>1,"",VLOOKUP(C3,data_validation!A:B,2)))

A validação de dados do P.S é apenas uma planilha diferente na qual eu crio todas as minhas validações e as uso para pesquisar o preço com base no número do produto.

    

Answer 1

Aqui está uma solução incorporando a sugestão de Lee para usar a função SUMPRODUCT (). Preencha esta fórmula de C3:

=IF(SUMPRODUCT((A$3:A3=A3)*(B$3:B3=B3))>1,"",VLOOKUP(B3,E$4:F$5,2))

Como funciona: As expressões A $ 3: A3 = A3 e B $ 3: B3 = B3 retornam matrizes de valores True / False que aumentam à medida que a fórmula é preenchida. Por exemplo, em C6, A $ 3: A6 = A6 é igual a {Falso; Falso; Falso; Verdadeiro}, pois a última posição é a única célula no intervalo que é igual a A6. Da mesma forma, B $ 3: B6 = B6 é igual a {Falso; Falso; Verdadeiro; Verdadeiro} desde que as duas últimas posições correspondem a B6.

Agora, a multiplicação dessas duas matrizes converte os valores True / False em 1 e 0 e executa o equivalente do AND () lógico. O resultado da multiplicação (em C6) é o array {0; 0; 0; 1}. SUMPRODUCT () adiciona os elementos dessa matriz, retornando 1.

Agora, a função IF () retorna o resultado do VLOOKUP, porque o resultado de SUMPRODUCT () não é > 1.

Olhando para a fórmula em C4, A $ 3: A4 = A4 é {Verdadeiro; Verdadeiro} e B $ 3: B4 = B4 também é {Verdadeiro; Verdadeiro}, então a multiplicação dá {1; 1} e SUMPRODUCT ( ) retorna 2, e o IF () retorna um espaço em branco.

EDIT para mostrar resultados:

Eu coloquei a tabela de consulta na mesma planilha que os dados. Você pode editar a equação para se referir à sua tabela de pesquisa.

Observação: se você achar essa resposta útil, clique na marca de seleção cinza no canto superior esquerdo para aceitar a resposta e removê-la da fila não atendida. Obrigado.