☰

Excel: a combinação INDEX( ,MATCH( ), )) funciona com intervalos, mas não com dados formatados como TABLE

tag-icon • tag-icon microsoft-excel
Excel: a combinação INDEX( ,MATCH( ), )) funciona com intervalos, mas não com dados formatados como TABLE

A configuração:

Tenho duas planilhas Excel com dados, ambas "Formatadas como tabela(s)". Um é um grande conjunto de dados, com muitas linhas e colunas (campos) e o outro é uma espécie de “dicionário”, com apenas 2 linhas e 2 colunas (campos).

Tabela 1 (conjunto de dados)

+----------------------------------------------------+
| month | week | productName | price     | sold pcs. |
+---------------------------------------------------+|
| jan   | 1    | heavy       | (formula) | 25        |
| jan   | 2    | heavy       | (formula) | 51        |
| jan   | 3    | heavy       | (formula) | 06        |
| jan   | 4    | heavy       | (formula) | 00        |
| jan   | 1    | light       | (formula) | 39        |
| jan   | 2    | light       | (formula) | 11        |
| jan   | 3    | light       | (formula) | 98        |

Mesa 2 (ditado)

+---------------------+
| productName | price |
+---------------------+
|  heavy      | 125   |
|  light      | 65    |

O que eu preciso fazer:

O que preciso fazer é trazer os valores (preços dos produtos) do dicionário para o grande conjunto de dados, onde certos valores (nomes de produtos) corresponder.

O que eu tentei:

=INDEX(Table2[@price], MATCH(Table1[@productName], Table2[@productName], 0))

Ao trabalhar com faixas, essa combinação funciona perfeitamente. Porém, quando faço assim, com dados formatados como tabelas, ele só corresponde na primeira linha, da Tabela1.

Este é o resultado que obtenho:

+-------------------------------------------------+
| month | week | productName | price  | sold pcs. |
+-------------------------------------------------+
| jan   | 1    | heavy       | 125    |  25       |
| jan   | 2    | heavy       | #N/A   |  51       |
| jan   | 3    | heavy       | #VALUE |  06       |
| jan   | 4    | heavy       | #VALUE |  00       |
| jan   | 1    | light       | #VALUE |  39       |
| jan   | 2    | light       | #VALUE |  11       |
| jan   | 3    | light       | #VALUE |  98       |

A primeira linha é correspondida corretamente, a segunda linha (novamente igual à primeira) não é encontrada e a partir daí os valores de erro começam. O que devo fazer?

Obrigado

Responder1

Você está usando indevidamente os intervalos nomeados:

  • Table2[@productName]aponta para um único elemento na tabela
  • Table2[productName]aponta para toda a coluna
  • Table2aponta para todas as colunas e linhas da tabela, excluídos os cabeçalhos

Você pode fazer isso facilmente com um VLOOKUP:

=INDEX(Table2,MATCH([@productName],Table2[productName],0),MATCH("price",Table2[#Headers],0))

Soluções alternativas:

=VLOOKUP([@productName],Table2,2,0)

=VLOOKUP([@productName],Table2,MATCH("price",Table2[#Headers]),0)

Ou, desde que cada productName seja exclusivo, você pode usar SUMIF:

=SUMIF(Table2[productName],[@productName],Table2[price])

Responder2

Isso deve funcionar para você:

=INDEX(Table2[[#All],[Price]],MATCH([@productName],Table2[[#All],[ProductName]],0),1)

Observação. o uso de #ALL seleciona a coluna inteira, não apenas uma LINHA.

informação relacionada