AMATCHNTH, AVLOOKUPNTH, option for random return value

[John Brown]

Hello, both AMATCHNTH, AVLOOKUPNTH take a Position parameter:

Position (Optional, Defaults to 0, AMATCHNTH only)

Controls which result will be returned from multiple matches.

N: where N is a positive integer. The Nth match found will be returned

0 : If sorted ascending then the largest value that is less than or equal to Lookup_Value
     If  not sorted then the first value found
     If sorted descending then the smallest value that is greater than or equal to lookup value

-1: The first value found will always be returned

-2: the Last Value found will always be returned

-3: All matches found will be returned

I didn't realize that -3 lets these functions imitate the behaviour of AMATCHES2 and AVLOOKUPS respectively. - Charles do you know of any difference between the behaviour of AMATCHNTH, AVLOOKUPNTH with Position = -3, and AMATCHES2 and AVLOOKUPS?

- If there is no difference, I will probably abandon using AMATCHES2 and AVLOOKUPS in favour of using AMATCHES2 and AVLOOKUPS, as I prefer functions that are more mutable [not sure if that's the right word], by changing their inputs.

Suggestions - a) the Help could be tweaked to make this more apparent.

Or really,

b) why have AMATCHES2 and AVLOOKUPS at all?

Anyway, none of the above is my real request, which is:

Please add a possible value for Position,

say, -4,

which causes the function to randomly return a value from the multiple possible matches, where there are multiples.

This would enable not having to mess around with RANDBETWEEN, etc, all for the sake of passing either -2 or -3 to the function, as a cheap/huck way of getting it to return a random value, which is not really very good, as it will only return the first or last value, and ignore 'middle values'. And yes I know I can solve that by using eg RANDBETWEEN in a smarter way, but that means caching the returned values in an expression, and getting their count, and passing that to RANDBETWEEN, etc etc, which leads to bulkier formulas, which definitely require LET.

Many thanks for considering this

John

[Charles Williams]

All the SpeedTools LOOKUP functions use thesame underlying code so the -3 option is the same as LOOKUPS. Its just there for simpler syntax and better discoverability.

I appreciate your RAND request, but these functions are already complex and you can acive it using RANDBETWEEN(largesr neg number, smaller neg number)