# Return parameter-based results

#### deciog

##### Active Member
Good Morning.

Lotus Spreadsheet, I have a problem finding a formula that returns the data explained in the spreadsheet, there are two results,

Can be in Excel 2016 or 365 version

I am grateful in advance for the help

I thank

Decio

#### Attachments

• 15.6 KB Views: 8

#### vletm

##### Excel Ninja
deciog
Something like this ...
... and without those right side data-tables.

#### Attachments

• 13.9 KB Views: 2
Last edited:

#### bosco_yip

##### Excel Ninja
Maybe,

[L13] : =MATCH(L2,\$AF2:\$AF7)

{L15] : =SMALL(\$L13:\$P13,COLUMN(A1))

All copied across right

Last edited:

#### Peter Bartholomew

##### Well-Known Member
@deciog
Is the fact that your array comprises a continuous sequence of natural numbers chance or intentional? If intentional

#### Peter Bartholomew

##### Well-Known Member
Hi @deciog
Never one to just leave a problem alone, I randomised the Array.

I wrote a simple formula to return the row number corresponding to a single value held in a cell. I then turned the formula into a Lambda function in order to be able to pass the cell reference as an argument. Passing a multicell array matches each value but sums the row numbers so is not useful.

That is where the going gets heavy. I believe that sooner or later there will be a built in MAP function that will run through a range or array and return an array comprising the results drawn from each cell in turn. Since the function does not exist at present, I emulated it using a recursive Lambda function (MAP). This takes both the array and the lambda function I wish to apply to the range as parameters.

Once I have the result as an array, I can further process it, in this case to use the SORT function to sort the columns.

#### Attachments

• 32.8 KB Views: 3

#### deciog

##### Active Member
@vletm, thank you very much

I loved its very simple logic, it works perfectly in the original spreadsheet
In cell L15 I was far from this simple logic, I need to study more

Thank you very much,

Decio

@Bosco, thank you very much

In cell L15 I was far from this simple logic, I need to study more
In cell L13 a logic that is also simple I loved it, I forgot the MATCH detail in the table

Thank you very much

Decio

@Peter, thank you very much

I like your logic a lot, I'm learning a lot, your explanations are great.

Thank you very much

Decio