Como encontrar todos os dados / dados correspondentes em um intervalo

Question

Estou tentando encontrar um número MAX e o ano em que ele pode ser relacionado.

• ANOS 2000-2010 (A1: J1)
• DATA (A2: J2)
• MAX de DATA é colocado em A3

Eu posso usar INDEX / MATCH para me dar o PRIMEIRO MAX ANO, onde ele corresponde ao MAX de DATA, mas esse valor MAX pode aparecer em vários anos (por exemplo, 2002, 2005, 2009).

PRIMEIRO ANO MÁXIMO em B3

Eu tenho uma fórmula no C3 que agora está tentando iniciar outra correspondência de índice usando (OFFSET, 0, B3) para fazer a pesquisa começar após o PRIMEIRO MAX ANO, mas me dá o primeiro ano de contato por algum motivo.

=INDEX(A1:J1,MATCH(A3,OFFSET(A2:J2,0,MATCH(B3, A1:J1,0))))

Eu arredondei meus números para que eles tenham uma casa decimal, e eu sei que tenho dois números MAX de DATA no meu intervalo A2: J2 ..

Atualização: uma imagem para explicar com mais clareza.

Algumaidéia?

ObrigadoMichael

RESPOSTA:

SeguiolinkdeAbdulnoscomentáriosabaixoeencontreiumafórmulaqueatendiamuitobemaosmeusrequisitos.

=IFERROR(ÍNDICE(A1:K1,PEQUENO(SE(A2:K2=$A$3,COLUNA(A2:K2)-COLUMN(ÍNDICE(A2:K2,1,1))+1),enésimo)),"")

A enésima célula é o número 2. Então, está procurando a segunda ocorrência da minha célula A3.

Espero que isso ajude as pessoas. E o Exceljet é um ótimo site. Altamente recomendo.

get-nth-match-com-index-match

    

Answer 1

Você poderia fazer isso com fórmulas inseridas apenas em B3, C3, etc. Mas sugiro adicionar uma linha auxiliar (linha 4) para tornar as fórmulas mais legíveis.

Na célula B4, digite:

=MATCH($A3,OFFSET($A2:$K2,0,A4),0)+A4

Isso retorna 6 , que é o número da coluna da primeira ocorrência de 19,0 na linha 2. Copie essa fórmula para a direita e você obterá 8 e 11 seguido por #N/A nas células restantes. Agora entre na célula B3:

=INDEX($A1:$K1,B4)

E copie a fórmula para a direita. Você obterá os anos em que o valor máximo foi encontrado: 2005 , 2007 e 2010 seguido por #N/A nas células restantes.

Se você não quiser ver os valores #N/A , poderá usar a função IFERROR para alterá-los para espaços em branco.

    

Answer 2

Eu gosto da resposta de Blackwood, já que não requer uma fórmula de matriz. Mas se você não puder suportar a linha auxiliar ou precisar fazer isso com apenas uma fórmula, poderá usar isso em A4 e preencher à direita:

 =IFERROR(SMALL(IF($A$2:$K$2=$A$3,$A$1:$K$1),COLUMN()),"")

É uma fórmula de matriz, por isso deve ser introduzida com CTRL Deslocar Introduzir , em vez de apenas Introduzir .

Veja como isso funciona. O IF () produz uma matriz dos anos em que os dados correspondem a A3 (o máximo dos dados) e FALSE em qualquer outro lugar:

{False;False;False;False;False;2005;False;2007;False;False;2010}

E, em seguida, SMALL () seleciona o primeiro, o segundo, o terceiro, etc., os menores valores desse array, usando o número da coluna como o índice. Ele verifica apenas os números e ignora os valores "False" na matriz.

Finalmente, IFERROR () substitui qualquer erro # N / A com um espaço em branco.