Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Table sorting

Copper Contributor

Hi 

I've compiled a comprehensive list of dates in an array (let's call it B1:XX50). Next, starting from A70, I successfully consolidated all the dates into a single column, arranged them in chronological order, and eliminated any blank cells (using a combination of Sort, transpose, filter and torow functions).

Now, my inquiry is as follows: In column B70, I want to retrieve the value from column A1:A50 that corresponds to the same row as the match of A70 in the array B1:XX50.

Tried multiple ways but could not get it right. 

NB: XLOOKUP works but only on a single column and not an array



Thank you in advance!

2 Replies

@Ireadbooks2 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

 

The data layout in the screenshot and in the attached file is for illustration. You can place the green result table anywhere to the right or below of the blue table or in another worksheet.

table sorting.png

Thank you Oliver for your responses... it is much appreciated!