Como posso fazer uma pesquisa bidimensional em que os cabeçalhos de coluna e linha são intervalos?

Question

Eu tenho uma planilha com uma tabela que é semelhante em estrutura a isso:

      | 1-2 | 3-4 | 5-6 | 7+
-------------------------------
18-20 |  7  |  2  |  9  |  4  |
21-25 |  1  |  0  |  6  |  3  |
26-30 |  1  |  4  |  6  |  9  |
31+   |  3  |  8  |  5  |  0  |

Por exemplo, as entradas que recebi do usuário foram 2, 19 , a pesquisa deve retornar 7 . Ou, se as entradas forem 9, 29 , a pesquisa deve retornar 9 .

Eu sei que você pode fazer pesquisas bidimensionais quando tiver apenas valores únicos nos cabeçalhos de linha e coluna, combinando VLOOKUP e MATCH . Mas como faço a pesquisa quando os cabeçalhos são um intervalo de valores?

Preciso apenas escrever uma função VBA personalizada ou o Excel tem alguma forma interna para fazer isso?

    

Answer 1

INDEX() pode fazer uma pesquisa bidimensional. A sintaxe é INDEX(array,row_num,[col_num],[area_num]) . É preciso um array 2D e retorna a interseção de row_num e column_num.

Seu problema é um pouco complicado por causa dos intervalos nos cabeçalhos, mas alguns trabalhos rápidos com TRUNC() e IF() fazem o trabalho. Esta fórmula preenchida de D8 na imagem abaixo, dá os resultados mostrados:

=INDEX(B$2:E$5,IF(C8>31,4,TRUNC((C8-11)/5)),IF(B8>7,4,TRUNC((B8+1)/2)))

Se você quiser ver como a parte TRUNC() funciona, coloque =TRUNC((ROW()-11)/5) na linha 18 ou TRUNC((ROW()+1)/2) na linha 1 e preencha.

Espero que isso ajude e boa sorte.

    

Answer 2

Dado que você está procurando em intervalos que são mutuamente exclusivos e completos (pelo menos para valores > = 18), seu intervalo pode ser definido pelo primeiro valor em cada célula. Por exemplo, a tabela que você fornece pode ser descrita de maneira equivalente por:

Vocêpodeentãoencontraroseuvalorcombinandovlookupematch.Porexemplo,seseusvaloresdetabelaepesquisaforamorganizadoscomoaseguinteimagem:

Seu valor desejado estaria na célula destacada que conteria o seguinte: = VLOOKUP (B $ 8, $ A $ 1: $ E $ 5, MATCH ($ A8, $ A $ 1: $ E $ 1,1), TRUE)