Was wondering if exists already a function to find where is positioned a number in a sorted array ( or sequence of numbers ) , something similar to the Excel VLOOKUP or is something that we need to develop by ourself with a small program ( just to avoid to reinvent the wheel).
For finding I mean where is the most closer match and its position ..like in Excel.

Thanks in advance

Franco

Thanks a lot for your feedback , in the meantime I have developed something to fulfill this need and to share if .... there is someone else in the same situation.
The function is called Vlookup(M,Val) and is in the snippet attached.
It looks for a value V in the single column vertical array M . The array MUST be sorted . The output values is a 2 values vertical array whose top position gives :

-the position where the values Val has been found if there is an exact match, in this case the bottom
position is at True(=1 means that there is an exact match)
-the position of where the values Val can be inserted if there is not an exact match , in this case the
bottom position is at
False(=0 means that there is not an exact match)

if the Val is not within the extremes of the lookup range there is a message that indicates this situation.
Probably is not developed in the most elegant and updated way but it does it work.

Best regards

Franco

@Ioan

thanks a lot for your snippet and for your suggestions.
However probably I haven't been very clear in describing what i was trying to replicate.
in the last example :

Val:=2.367

I am not trying to find the closer match of this value with what is reported in the array M'
( that is what is done in your program ) but just the position in which it could be inserted .

So in my program the position 10 and the fact that found is equal to 0 ( i.e is False ) means that the binary search didn't lead to a match but that if this value could be inserted into the array M' the correct position would be the 10th , shifting all the other values downside.

This way I know that in position 10 there is a value that is higher that Val and that If there are other info's linked to the numerical values reported in M' I can obtain this values for instance through an interpolation of the lower and upper value of the interval in which this value is bounded ( and the upper bound is the value at position 10 and the lower one at position 9 )

@Martin

your solution is very elegant and due to my newbieness I haven' fully understood it, so in the next days I have to analyize it better and feel more comfortable with it .
Your approach is very good to understand if something is find in the array and where ..but for what concern the desire to understand where it would be inserted if it was possible ( so for other applications ) is not the best ..but could be useful toghether with other testing in a more elaborate solution.

However another issue I am trying to address is lookup a value when there is a duplication of data in the array . I have seen that my solution doesn't give a consistent result so I am studying deeply this problem and trying to solve it in a more complete way ( is always the desire to emulate completely the VLOOKUP of Excel style )
See this link for a more detailed discussion of this topic.
http://www.sascommunity.org/sug...12%20Drummond.pdfhttp://

Thanks again and best regards.

Franco