• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Peter Bartholomew

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

74258

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

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
 
Top