@
bosco_yip .
Hello Bosco. Yes, I fully agree. I guess there is a decision to be made about how one treats unstated requirements. The binary search has substantial advantages if the dataset approaches 100,000 rows but the dataset has to be sorted.
As you will have realised, an unsorted list would require the formulas referred to by 'activeRow' and the populated version 'populatedRow' to be changed to 'exact match'
= INDEX( selections, MATCH( required, rowLabel, 0 )
= INDEX( selectedFruit, MATCH( required, rowLabel, 0 )
by adding the third, '0', parameter. As for the version issue, I had to switch from my Office 2010 desktop to an Office 365 laptop to prove the formula. I have long had a grudge concerning the gross inadequacy of the CONCATENATE function so it was good to be able to exploit TEXTJOIN/CONCAT.
The definition of 'selectedFruit' is also a little unusual in that it combines a 2D array of criteria with a row of results to populate a 2D range of results. I am not sure that I have done that before.
Kind Regards
Peter