Como criar uma fórmula do Excel que encontre o intervalo correspondente em uma matriz para um determinado valor?

Question

Gostaria de criar uma fórmula do Microsoft Excel que encontre o intervalo correspondente em uma matriz para um determinado valor.

Explicado com um exemplo genérico

Tabela de referência

| Name | From | To |
| A    |    1 |  5 |
| B    |    6 |  8 |
| C    |    9 |  9 |

Tabela de origem

| Name | Value |
|      |     0 |
|      |     4 |
|      |     9 |
  ^
  Field is getting "calculated".

Tabela de resultados

| Name | Value |
|      |     0 |
| A    |     4 |
| B    |     6 |
| C    |     9 |
  ^
  Field is getting "calculated".

Explicação específica

Na minha tabela de referência eu tenho freqüências com informações em que banda é a faixa de freqüência e quantos Watts estou autorizado a usar.

Na minha tabela de fonte / resultado (o mesmo) eu tenho uma lista de frequência e gostaria de ter as informações de banda e Watts automaticamente atribuídas a ela.

Tabela de referência

|  Band | Frequency Range | Watts |
| 160 m |  1.810 |  1.850 |   100 |
| 160 m |  1.890 |  2.000 |    10 |

Tabela de resultados esperados

             First part is source field for the look up.
             v
|  Band | Frequency Range | Watts | Description |
| 160 m |  1.811 |  1.811 |   100 | Foo Bar     |
| 160 m |  1.891 |  1.897 |    10 | Lorem ipsum |
    ^                         ^
    Field is "calculated".    Field is "calculated".

Conclusão

Como definir (quando é possível a todos devido ao intervalo) uma fórmula do Excel que escolha a linha de referência da matriz correta de uma matriz enquanto procura um único valor dentro de um intervalo da matriz?

A fórmula deve ser compatível com o Excel 97, mas, no mínimo, com o Excel 2000.

    

Answer 1

Basicamente, você precisa usar VLOOKUP para obter essas informações.

Na primeira cópia, mova sua coluna "de" para a mais à esquerda, para que suas colunas sejam organizadas: A (de), B (nome), C (para).

Se você tiver um intervalo contínuo (por exemplo, você pode considerar 1.870 no seu exemplo como membro do primeiro grupo), sua fórmula (em A2 da sua tabela de resultados): =IFERROR(VLOOKUP(B2,reference_sheet!A:C,2,true),"")

Se você deseja obter valores vazios quando sua frequência está entre os intervalos, a sua fórmula será mais complicada (ainda usando referências de acordo com o exemplo geral):

• primeiro pegue a linha do seu grupo ignorando o limite superior: =IFERROR(MATCH(B2,reference_sheet!A:C,1),"") - coloque isso em, por exemplo. coluna C

• verifique se seu valor está dentro do intervalo: =IFERROR(B2<=index(reference_sheet!C:C,C2),FALSE)

Você tem agora o número da linha na coluna D e TRUE na coluna E para cada uma de suas entradas, com a função INDEX agora você pode coletar os dados de que precisa. (também é possível fazer tudo sem qualquer coluna auxiliar, mas as fórmulas serão muito mais complexas nesse caso)

    

Answer 2

Se você desejar alterar sua tabela de referência de forma que a coluna de intervalo seja a coluna mais à esquerda, a função VLOOKUP deverá fazer o que você deseja. Como você está tentando corresponder a intervalos, em vez de valores específicos, verifique se sua tabela de referência está na ordem classificada por intervalo e passe TRUE para VLOOKUP como o quarto parâmetro.

Se você não quiser reorganizar a tabela de referência para posicionar os intervalos à esquerda, poderá obter resultados semelhantes usando uma combinação das funções MATCH e INDEX .

Estas funções estão todas disponíveis no Excel 2007 e versões posteriores.