Fórmula genérica
=INDEX(CHOOSE(number,array1,array2),MATCH(value,range,0))
Resumo
Para configurar uma fórmula INDEX e MATCH onde a matriz fornecida para INDEX é variável, você pode usar a função CHOOSE. No exemplo mostrado, a fórmula em I5, copiada, é:
=INDEX(CHOOSE(H5,Table1,Table2),MATCH(G5,Table1(Model),0),2)
Com Tabela1 e Tabela2 conforme indicado na imagem.
Explicação
Basicamente, esta é uma função INDEX e MATCH normal:
=INDEX(array,MATCH(value,range,0))
Onde a função MATCH é usada para encontrar a linha correta para retornar do array, e a função INDEX retorna o valor naquele array.
No entanto, neste caso, queremos fazer a variável de array, de modo que o intervalo dado a INDEX possa ser alterado em tempo real. Fazemos isso com a função CHOOSE:
CHOOSE(H5,Table1,Table2)
A função CHOOSE retorna um valor de uma lista usando uma determinada posição ou índice. O valor pode ser uma constante, uma referência de célula, uma matriz ou um intervalo. No exemplo, o índice numérico é fornecido na coluna H. Quando o número do índice é 1, usamos a Tabela1. Quando o índice é 2, alimentamos a Tabela 2 para INDEX:
CHOOSE(1,Table1,Table2) // returns Table1 CHOOSE(2,Table1,Table2) // returns Table2
Nota: os intervalos fornecidos para CHOOSE não precisam ser tabelas ou intervalos nomeados.
Em I5, o número na coluna H é 1, então CHOOSE retorna a Tabela 1 e a fórmula é resolvida para:
=INDEX(Table1,MATCH("A",Table1(Model),0),2)
A função MATCH retorna a posição de "A" na Tabela1, que é 1, e INDEX retorna o valor na linha 1, coluna 2 da Tabela1, que é $ 20,00
=INDEX(Table1,1,2) // returns $20.00
