Compile multiple worksheets data into one

[etax]

Hi,

I have a lot of data in multiple worksheets.What I want through dynamic array spill formula is that is to get
1.Unique values from multiple worksheets of D6 and below column in a Summary worksheet in D6 (reference might gets change in my actual worskheet)
2.Secondly,Get the value of C Column of each worksheet by match D6 of Summary worksheet which is received from 1.Here,get from 1st worskheet,if not match from 1st worksheet then lookup in 2nd worksheet and so on. The value so obtained in C6 column of Summary worksheet should be sorted alphabetically.
3.Unmatch data of D6,would be placed on E6,F6,G6,H6 and below
4.Get all the worksheets data in Summary Worksheet from I to P

Pls not I don't want in VBA.
I am using office 2021/365 version.
Link >file

 

[xlyfe]

You can do this using the LET function, but in order for it to work with data from multiple worksheets, you would have to run some vba which loops through each sheets specific range to copy those values back to the "summary" page so that you have one giant list of values that you can then use the LET function on. Hopefully this can help guide you in the right direction.

 

[etax]

Kindly share me.