@
NARAYANK991, i see how others might have been mislead by what i wrote before. Here's an attempt to give some more details and explain results from your sample data but i fear my explanations are much less enlightening than yours.
First, regarding VLOOKUP with Range_Lookup=TRUE, i agree this makes little sense on unsorted data and generates potentially misleading results (unless the lookup value is very large.) The only point of doing so is to try to uncover the method that is being used. Others already gave fairly detailed explanations to make sense of the seemingly random results. After following these explanations and some further experimentation, this is essentially my understanding of the algorithm that is used:
We start with an array of numbers and a lookup value. Then we select a number at the midpoint of the array. The lookup value is compared with that number and its successor. If the lookup value falls between the number and its successor, the number is returned. If the lookup value does not fall in this interval we eliminate the array of values on the left side if the lookup value is larger than the number or else eliminate the array of values on the right side if the lookup value is larger than the successor. Then we repeat the process from the start. In the end a final value is reached and if the lookup value is greater than this final value the final value is returned, else an error is thrown.
Turning to the sample data, i have attempted to list the supposed paths of numbers taken to reach the result by applying this method.
=LOOKUP(Lookup_Value,{85;44;79;83;42;43;15;31;5;54},{1;2;3;4;5;6;7;8;9;10})
53: 9 [42 -> 31 -> 5]
17: #N/A [42 -> 44 -> 85]
85: 10 [42 -> 31 -> 5 -> 54]
With sorted data the results make more sense! The second formula i posted above effectively automates the sort process.
=LOOKUP(Lookup_Value,{5;15;31;42;43;44;54;79;83;85},{9;7;8;5;6;2;10;3;4;1})
53: 2 [43 -> 79 -> 44]
17: 7 [43 -> 15]
85: 1 [43 -> 79 -> 83 -> 85]
In terms of efficiency, the second formula seems to be roughly linear time and can be used ok on an entire column. Sriva's formula would also be fine on small data sets but sorting the array using SMALL involves doing binary compares on all elements i.e. quadratic time.
By comparison, the first formula would be expected to be much faster (logarithmic). However, it seems to be not fully optimal in some cases. For example if Column A consists entirely of zeros, filling down =LOOKUP(0,A:A) takes many seconds whereas filling =LOOKUP(1,A:A) is near instantaneous. This suggests that when many successive values are equal to the lookup value these are iterated through linearly.