Localizar dados de coluna e linha correspondentes com base nos dados de outra célula no Excel

Question

Ok, eu tenho um arquivo excel com algumas tabelas como a da imagem e no link abaixo.

Captura de tela da tabela

link

O que estou tentando fazer é inserir um valor que esteja no intervalo D2: N21 e fazer com que ele retorne o valor correspondente de uma célula nas colunas B & D e a data de Linha 1 e tê-los em células B26 , C26 & D26 respectivamente.

Eu tentei usar fórmulas INDEX MATCH , mas parece que não consigo dar certo. Estou supondo que estou perdendo alguma coisa. Qualquer ajuda seria muito apreciada.

    

Answer 1

Isso pode ser feito sem usar nenhum VBA. No entanto, é necessário mais do que apenas as funções Lookup / Reference, pois elas funcionam apenas em uma dimensão (como a maioria das outras funções).

SUMPRODUCT() é uma função que funciona com matrizes bidimensionais (que podem ser geradas usando apenas uma comparação simples).

A solução requer apenas as duas fórmulas seguintes:

Fórmula 1 Inserida em B26 e ctrl-entered / preenchida / copiada-colada em B26:C26 :

=IF(ISERROR($D26),NA(),INDEX(B:B,SUMPRODUCT(MAX(($D$2:$N$21=$A26)*(ROW($D$2:$N$21))))))

Fórmula 2 Inscrito em D26 :

=INDEX($1:$1,COLUMN($D$2:$N$21)-1+MATCH($A26,INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),1):INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),COLUMNS($D$2:$N$21)),0))

A versão prettificada da fórmula 2 é:

=
INDEX(
  ($1:$1),
  COLUMN($D$2:$N$21)-1
  +MATCH(
    $A26,
    INDEX(
      $D$2:$N$21,
      MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
      1
    )
    :INDEX(
      $D$2:$N$21,
      MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
      COLUMNS($D$2:$N$21)
    ),
    0
  )
)

Observe que, se os valores em dólar da tabela fossem únicos, uma fórmula menos complexa poderia ser usada. Como você não especificou o que gostaria de fazer quando as duplicatas são encontradas, escrevi a fórmula mais simples que ainda funciona quando são encontradas.

Essa fórmula apenas extrai o valor correspondente mais à esquerda na linha mais inferior que contém uma correspondência. Pode ser modificado para retornar certos valores alternativos específicos.

A fórmula a seguir pode ser usada para detectar se há duplicatas na tabela para o valor inserido em A26 , se alguma notificação / ação for necessária:

=SUMPRODUCT(MAX((D2:N21=A26)*(ROW(D2:N21))))<>SUMPRODUCT(SUM((D2:N21=A26)*(ROW(D2:N21))))

Para o caso de valores únicos, a função MAX() na fórmula 1 não seria mais necessária, é claro, e a fórmula simplificada 2 seria:

=INDEX($1:$1,IFERROR(1/(1/(SUMPRODUCT(($D$2:$N$21=A26)*(COLUMN($D$2:$N$21))))),NA()))

Notas:

• A fórmula prettificada realmente funciona se inserida.
• Os parênteses em torno de ($1:$1) na versão prettified são necessários para forçar o $1:$1 a permanecer em sua própria linha.
• Embora eu tenha escolhido exibir o erro #N/A se o valor em dólar inserido não puder ser encontrado, isso poderá ser alterado para qualquer outra coisa.

Answer 2

Acho que todas as funções de pesquisa / referência pesquisam colunas ou linhas. Eu usaria "funções personalizadas", que são funções VBA que entram em um módulo. Você precisaria de 3, um para B26, C26 e D26. Aqui está um para o D26 (Data). No D26 você teria "= zDate (A26)". O módulo:

Option Explicit

Function zDate$(param$)
  Dim icol&, searchRange As Range, cellRange As Range
  Set searchRange = Range("D2:N21")
  Set cellRange = searchRange.Find(param, , xlValues, xlWhole)
  icol = cellRange.Column ' column of found cell
  zDate = Cells(1, icol) ' returns date
End Function

EDIT: Que resposta incrível do @robinCTS. Eu vou comprar o livro de acompanhamento. Eu fui movido para criar

=INDEX(A1:N21,1,MAX(SUMPRODUCT((D2:N21=A26)*(COLUMN(D2:N21)) )))

mas ele não verifica se há dups e não entendi o formato "$ bbbnn.nn". No meu, A26 teria que incluir o "$" e espaços. Obrigado