Multi-coluna condicional INDEX MATCH

Question

Eu tenho duas planilhas, uma com duas colunas de valores como esta:

Num1    Num2    Result
0.01    0.99    
0.01    0.98    
0.01    0.95    
0.01    0.95    
0.01    0.94    
0.01    0.93    
0.01    0.91    
0.01    0.91    
0.01    0.91    
0.01    0.91    
0.01    0.9 
0.01    0.89    
0.01    0.87    
0.01    0.84    
...     ... 

E um com duas colunas e um valor para pesquisar, assim:

Num1    Num2    Country
0.01    0.99    Norway
0.01    0.80    Slovenia
0.01    0.41    Ukraine
0.02    0.65    Belarus
0.03    0.79    Slovakia
0.04    0.90    Iceland
0.04    0.72    Kazakhstan
0.05    0.88    Finland
...     ...     ...

Meu resultado desejado é preencher a coluna C na Folha 1 com o país (da Folha 2) cujos valores Num1 e Num2 correspondem mais aos valores das colunas A e B. Apenas a partir da análise dos dados, posso ver que as correspondências de pares exatos são vai ser raro, por isso precisa ser "fuzzy".

Isto é, tanto quanto eu tenho em termos de uma fórmula para a coluna C:

=INDEX(Lookup!$C$2:$C$123,MATCH(TRUE,INDEX(ABS(Lookup!$B$2:$B$123-Sheet1!B2)=MIN(INDEX(ABS(Lookup!$B$2:$B$123-Sheet1!B2),,)),,),0))

Obviamente, isso não é o que eu estou procurando, porque não é condicional nas colunas Num1 correspondentes, o que eu acho que precisa ser. Mas meu pensamento era que eu poderia fazer isso condicionalmente para Num1, então fazê-lo novamente em outra coluna para Num2, e então fazer algo inteligente com Vlookups para descobrir os deltas entre os pares de Num1s e Num2s. Então eu poderia usar esses deltas para selecionar a correspondência condicional que melhor refletisse o valor "mais próximo".

Tenho a sensação de que há uma maneira melhor de fazer isso, mas não consigo nem passar do primeiro passo. Obrigado!

    

Answer 1

Supondo que a tabela de resultados e a tabela de origem estão em Sheet1!A1:C15 e Sheet2!A1:C9 respectivamente (ambos com cabeçalhos na linha 1), em Sheet1!C2 , fórmula de matriz ** :

=INDEX(Sheet2!C$2:C$9,MATCH(MIN(IF(ABS(Sheet2!A$2:A$9-A2)=MIN(ABS(Sheet2!A$2:A$9-A2)),ABS(Sheet2!B$2:B$9-B2))),IF(ABS(Sheet2!A$2:A$9-A2)=MIN(ABS(Sheet2!A$2:A$9-A2)),ABS(Sheet2!B$2:B$9-B2)),0))

Copie conforme necessário.

Atenciosamente

** As fórmulas das matrizes não são inseridas da mesma maneira que as fórmulas 'padrão'. Em vez de pressionar apenas ENTER, você primeiro pressiona CTRL e SHIFT, e só então pressiona ENTER. Se você fez isso corretamente, você notará que o Excel coloca as chaves {} ao redor da fórmula (embora não tente inseri-las manualmente).