I would like to share an example of function that extracts rows from a data table based on set value in selected columns:
VLOOKUPdataparse.sm (18kb) downloaded 48 time(s).

Very nice

I think the issue comes out because there isn't a way to stop the preprocessing in user-defined functions when you pass an unknown (and an unknown is not a valid input).

A workaround that might be useful even to avoid the use of other functions is to use a string as input in the second row, and a check in your lookup like this:

Edited by user 03 January 2017 13:08:28(UTC)  | Reason: Not specified

It is by design. However even returning itself, you will have always var=findstr(...) -> false -> ... (it doesn't prevents the strcont() function to be evaluated)


Yes, but with functions created on the canvas this isn't possible


You can try with this:


strcont() is defined locally to avoid the preprocessing on the canvas
try() function is added because when you use el(...) inside the lookup function, all the elements of the condition matrix are evaluated -> you have to allow any type of input as first argument or let it fails silently (because strrep requires strings); note that if the selected element of the condition matrix is the one that contains strcon() with a wrong type as first argument, the error string will trigger an error on bool#:eval(bool#*operand#), because operand# will be a string.

Edited by user 03 January 2017 18:45:32(UTC)  | Reason: Not specified

Thanks, Jean. I havent used "truth tables" yet; they seem to work quite well.

Hello Alex, can you attach an example where stack it is so slow?

Here it is - I use EXCEL to input 10000 x 13 matrix into SMATH - 1.2 seconds. Vlookup() loop to stack 400 rows - 15 seconds. Vlookup() loop which does not stack anything is 3 seconds.

VLOOKUPslow.sm (28kb) downloaded 36 time(s).
DATA.xlsx (1,652kb) downloaded 48 time(s).

Edited by user 09 January 2017 20:02:23(UTC)  | Reason: Not specified

Much better, thanks.

Could you explain how does the following exactly work:

resultrng#:el(data#,matches,range(1,cols(data#)))

thank you for sharing these function, lookup is another essential features of spreadsheet and could be better if it has to be include in standard distribution.

from vlookupfast.sm modification, may it be simplified for such a situation below. in case condition only use boolean 'equal to' as the same in any spreadsheet function.

Edited by user 23 April 2021 06:26:24(UTC)  | Reason: Not specified

... visit this intelligent/advanced lookup.

Utilities Matrix Locate Array1 Array2.sm (71kb) downloaded 29 time(s).