Index / Match - na tabela contendo vários valores semelhantes

Question

[! [insira a descrição da imagem aqui] [2]] [2]

Finalidade :

Use um valor de "comprimento" conhecido para encontrar o cabeçalho de coluna correto e, em seguida, procure um valor na coluna e, em seguida, retorne o índice à direita dessa tabela de dados.

Plano de fundo :

1. Alguns valores no corpo da tabela são repetidos em colunas diferentes
2. Os valores no corpo da tabela representam limites inferiores de intervalos
3. Os cabeçalhos das colunas em C4 a F4 representam limites inferiores de intervalos
4. Os cabeçalhos de linha em B5 a B8 representam um índice exclusivo

TABELA:

A   B   C   D   E   F   G   H   I   J   K               
3           Length                      Header                  
4       **4.1   5   5.3 6**     Length: 5.4 5.3 =HLOOKUP($J$4;C4:F4;1)          
5   **7**   18  20  21  23      Strength    28              
9   **20**  22  23  25      Index   11  =INDEX(B5:B8;MATCH(J5;E5:E8;4))                 
7   **11**  24  25  27  28                                  
8   **16**  27  28  30  31  

Já alcançado :

1. As informações em J4 & J5 é sempre dado (isto é, conhecido)
2. HLOOPKUP (J4) é usado para identificar o cabeçalho correto da coluna - e o resultado correto aparece em K4, ou seja, a coluna E
3. A fórmula (INDEX / MATCH) em J6, em seguida, pesquisa corretamente os valores sob o cabeçalho da coluna E (E5: E8 - que eu tenho que digitar) -
   e identifica corretamente que "28" (J5) cai no intervalo "27" (E7) - e, em seguida, identifica corretamente o cabeçalho da linha correspondente como "11" (B7)

Assistência necessária :

1. Atualmente, primeiro preciso executar a fórmula HLOOKUP para determinar o cabeçalho da coluna
2. Em seguida, tenho que digitar manualmente os valores da coluna (excluindo o cabeçalho da coluna) - com base no resultado de K4, na fórmula em J6 para executar a função INDEX / MATCH
3. Portanto, preciso de ajuda para inserir automaticamente os valores da coluna (excluindo o cabeçalho da coluna) com base no resultado HLOOKUP na fórmula INDEX / MATCH.

4. Eu preferiria usar funções em vez de VBA se possível

   [Screenshot of errors] [1] A captura de tela mostra como alguns cálculos fornecem um resultado # N / A.

Cumprimentos. Rudi

    

Answer 1

Use OFFSET para alterar o intervalo da célula superior à esquerda para obter o intervalo correto.

=INDEX($B$5:$B$8, MATCH($J$5, OFFSET($C$5, 0, MATCH($J$4, $C$4:$F$4, 1)-1, ROWS($B$5:$B$8), 1), 1))

Explicação

Isso se comporta como sua fórmula INDEX MATCH , mas eu mudei o E5:E8 em seu exemplo para um intervalo relativo, definido por OFFSET( ... )

OFFSET($C$5, //Reference point
       0,    //Rows shifted below, in this case it doesn't shift the row
       MATCH($J$4, $C$4:$F$4, 1)-1, //Column shifted to the right, in this case it shifts from '1-4' minus 1.
       ROWS($B$5:$B$8),           //Height, in this case you want as many rows as the table has, taken from the left "header"
       1)    //Width, in this case you only want one column

$C$5 é a célula superior à esquerda nos seus valores que é usada como um ponto de referência.

MATCH($J$4, $C$4:$F$4, 1)-1 é para obter o índice do cabeçalho da coluna. Isso é semelhante à sua fórmula HLOOKUP , mas retorna o número da coluna (a terceira coluna retorna 2, por exemplo)

ROWS($B$5:$B$8) é obter quantas linhas a tabela possui, tiradas de quantas linhas o "cabeçalho" esquerdo tem