Por demanda popular ... :-), além de alguns problemas com a versão INDIRECT , que eu descrevo abaixo.
Primeiro, duas outras soluções. As únicas diferenças substantivas são como o segundo intervalo a pesquisar é calculado. Andi usou INDIRECT , eu uso INDEX e OFFSET , respectivamente. Uma outra pequena diferença que você notará, em vez de codificar o valor a ser pesquisado, eu obtenho de uma célula, o que torna mais fácil testar as fórmulas variando o número que está sendo pesquisado. Eu incluí todos os três aqui, então é fácil vê-los próximos um do outro, o primeiro é copiado da resposta do Andi (com o 0 codificado permanentemente substituído pela mesma referência de célula que eu uso nos outros, B3):
=MATCH(B3,INDIRECT("R1C"&MATCH(B3,B1:G1,0)+1&":R1C7",0),0)+MATCH(B3,B1:G1,0)
=MATCH(B3,INDEX(B1:G1,0,MATCH(B3,B1:G1,0)+1):G1,0)+MATCH(B3,B1:G1,0)
=MATCH(B3,OFFSET(B1:G1,0,MATCH(B3,B1:G1,0)):G1,0)+MATCH(B3,B1:G1,0)
O segundo argumento para o primeiro (na fórmula, corresponde à segunda ocorrência do número de pesquisa) MATCH é a diferença:
INDIRECT("R1C"&MATCH(B3,B1:G1,0)+1&":R1C7",0)
INDEX(B1:G1,0,MATCH(B3,B1:G1,0)+1):G1
OFFSET(B1:G1,0,MATCH(B3,B1:G1,0)):G1
Andi descreveu o primeiro. O segundo e o terceiro trabalho calculam uma referência à célula após a correspondência (o INDEX(...) e OFFSET(...) ) e, em seguida, estendem isso para um intervalo que termina com a última célula (a :G1 part). Portanto, se pesquisar por 0, MATCH(B3,B1:G1,0) retornará 2, então a versão INDEX se tornará:
INDEX(B1:G1,0,2+1):G1
INDEX(B1:G1,0,3):G1
D1:G1
A versão OFFSET se comporta de maneira semelhante.
Embora os três sejam equivalentes, há duas coisas que não me permitiriam usar a versão INDIRECT . A primeira é que, porque calcula uma coluna absoluta, não funciona em circunstâncias ligeiramente diferentes. Digamos que o valor final na tabela seja alterado de 9 para 3. Então, se colocarmos 3 em B3 para procurar o segundo 3, todas as fórmulas devem retornar 6. No entanto, a versão INDIRECT retorna 5. É por isso . Nesse caso, MATCH(B3,B1:G1,0) retorna 4, então o INDIRECT se torna:
INDIRECT("R1C"&4+1&":R1C7",0)
INDIRECT("R1C"&5&":R1C7",0)
INDIRECT("R1C5:R1C7",0)
que é equivalente a E1: G1. No entanto, a primeira célula de E1: G1 é a célula que contém os 3 primeiros, portanto, o MATCH encontrará apenas os 3 primeiros novamente e retornará 1, que é adicionado ao primeiro valor MATCH (4), retornando 5.
A questão é que o cálculo da coluna é absoluto e não leva em conta o fato de que a tabela que está sendo pesquisada começa na segunda coluna. Assim, mesmo quando pesquisando por 0, a fórmula INDIRECT está "errada", em que está encontrando o primeiro 0 duas vezes, mas parece que funciona porque o segundo 0 está imediatamente no primeiro 0, então o MATCH está para o segundo 0 retorna 1, que é a diferença entre os números das colunas dos dois 0s. Quando a diferença entre os números das colunas dos dois números pesquisados não for 1, a fórmula INDIRECT retornará o valor errado.
Isso é solucionável ao tornar a referência de coluna relativa (e supondo que a fórmula esteja na mesma coluna que o início da tabela):
=MATCH(B3,INDIRECT("R1C["&MATCH(B3,B1:G1,0)&"]:R1C7",0),0)+MATCH(B3,B1:G1,0)
Agora, o INDIRECT é:
INDIRECT("R1C["&4&"]:R1C7",0)
INDIRECT("R1C[4]:R1C7",0)
que é F1: G1, então agora o segundo MATCH começará a pesquisar na célula à direita da primeira correspondência e retornará 2, resultando em um total de 6, o que é correto.
Isso funciona, mas ainda há um problema relacionado ao meu parênteses "supondo que a fórmula esteja na mesma coluna que o início da tabela". A versão INDIRECT (mesmo a fixa) é bastante frágil. Por exemplo, se eu inserir uma nova linha acima da tabela de números, as versões INDEX e OFFSET continuarão funcionando, porque o Excel atualizará automaticamente todas as referências. Mas como todas, exceto uma das referências na versão INDIRECT , são texto, o Excel não pode atualizá-las, então elas continuarão a se referir à primeira linha, que agora é outra. Se você remover a primeira coluna ou adicionar colunas adicionais à esquerda da tabela, haverá problemas semelhantes. Então, nesse caso, eu provavelmente optaria pela versão INDEX ou OFFSET , apenas para "testar futuramente" a planilha um pouco.