Como pegar um conjunto de valores e compará-lo com várias linhas de critérios

Question

Exemplo do que eu gostaria de alcançar:

A  B  C   D  E  F
1  1  <=  2  2  1
2  2  <=  4  23 8
3  4  <=  7  5  3
4  7  <=  10 40 10
5  10 <=  12 7  3
6  12 <=  17 8  4
7  17 <=  19 12 5

Eu gostaria de pegar cada número da coluna E e então determinar a qual linha A pertence, se o critério é que o número E é > B e < = D (usando toda a faixa B1: D999 que tem valores diferentes em cada linha).

A coluna F retornaria então o número da linha A para os números da coluna E.

Se todas as linhas das colunas B e D forem meus critérios como em B1 < = D1, B2 < = D2, B3 < = D3 etc., gostaria de comparar cada número na coluna E e ver em que intervalo eles se enquadram para dentro. O exemplo seria E6 = 40, esse número cai na faixa B12 < = D12 que tem um valor de 10 na linha A12). Então, eu gostaria de obter esse número de uma linha na célula F6 ao lado do E6. Eu tenho cerca de 500 números e eles também incluem números decimais em colunas B, D e E e uma coluna só tem números inteiros.

Linha A3 significa números de 1 a incluindo 2, linha A4, em seguida, significa números maiores que 2 para incluir 4. Portanto, o número E3 é maior que o número na coluna B3 e menor ou igual que o número na coluna D3, portanto cai no intervalo na linha 3, então o valor F é o mesmo que A3 = 1.

    

Answer 1

Esta é uma variação da abordagem do p. phidot . Fazer uma pesquisa na direção ascendente captura o intervalo incorreto e o ajuste para valores de intervalo médio gera resultados para valores no limite do intervalo. Um MATCH normal funciona se você fizer uma pesquisa descendente.

Classifique a tabela na coluna A, B ou D descendente. A fórmula em F3 seria:

=INDEX(A:A,MATCH(E3,D:D,-1),)

Se houver outro conteúdo nessas colunas, faça o intervalo referenciar endereços absolutos, como $ A $ 3: $ A $ 12 e $ D $ 3: $ D $ 12. Isso só precisa das colunas A e D para pesquisa, então não me incomodei em preencher o conteúdo da coluna B ou C no meu exemplo:

SeasuacolunaAéapenasumidentificadordelinhadatabelaeissopodepermaneceremordemcrescente,issopodeatésersimplificado.MATCHretornaonúmerodesequêncianatabela,quecorrespondeàcolunaAemordemcrescente,portanto,afórmulapodeserreduzidapara:

=MATCH(E3,$D$3:$D$12,-1)

Observe que, para o resultado da CORRESP para corresponder à coluna Um índice de linha de tabela, o intervalo de pesquisa precisa ser especificado com referências absolutas.

    

Answer 2

tente isso em F3:

=IFERROR(INDEX(A:A,MATCH(E3,D:D,0)),INDEX(A:A,MATCH(E3,D:D,1))+1)

mas acho que você terá que adicionar:

A1  ---> -1
A2  ---> 0
D1  ---> 0
D2  ---> 1

para cobrir o "intervalo ausente".