I have two worksheets, one with two columns of values like this:
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
... ...
And one with two columns and a value to look up, like this:
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
... ... ...
My desired result is to populate column C in Sheet 1 with the country (from Sheet 2) whose Num1 and Num2 values most closely match those in columns A and B. Just from eyeballing the data I can see that exact pair matches are going to be rare, so it needs to be "fuzzy."
This is as far as I've got in terms of a formula for column 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))
Obviously this isn't what I'm after, because it's not conditional on the Num1 columns matching, which I think it needs to be. But my thinking was that I could do this conditionally for Num1, then do it again in another column for Num2, and then do something clever with Vlookups to figure out the deltas between the pairs of Num1s and Num2s. Then I could use those deltas to select the conditional match that best reflected the "closest" value.
I have a feeling there's a better way to do that last bit, but I can't even get past step one. Thanks!
