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

I think that I can add a findvalue function (like the built-in findstr)...

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

---

This might also work:

[MATH lang=ENG]Vlookup(M,x):findrows(eval(augment(M,range(1,length(M)))),x,1)[/MATH]

@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/sugi/SUGI92/Sugi-92-112%20Drummond.pdfhttp://

Thanks again and best regards.

Franco

Here is a crude version of VLOOKUP (exact match only, scalar key values only). The parameters are:
key: the value to search for
M: the data table
c.key: the column index where to search for key
c.val: the column index where to get the return value

A plugin version might provide the following features:
- if c.val=0, then the row index of matches is returned. In the attachment this is done by appending a column with corresponding numbers the table. This could be done easily inside the function.
- allow for lists or matrices as key argument. The return value then would be an array of same size and type.
- a flag to choose between exact match closest match and linear interpolation
- a flag to choose extrapolation in case interpolation is choosen (constant or linear extrapolation)

---

findrows.sm (10 КиБ) скачан 67 раз(а).