Forum Discussion

swexcelnurd's avatar
swexcelnurd
Copper Contributor
Dec 11, 2025

Formula to retrieve data from several sheets and return values from given criteria.

I am trying to get data from each of our areas of the school staff timetables EY  MY  LY and summaries what days each staff member works where to another sheet, as staff move around.   I would apprec...
excel
office 365
IlirU's avatar
IlirU
Brass Contributor
Dec 15, 2025

Hi swexcelnurd,

 

Based on the example given by SnowMan55 I created the following formula which you can apply it in an empty cell of the Summary1 sheet. In order for the formula to be applicable you need to use Excel 365 or Excel for Web.

 

=LET(ey, EY!C3:G10, my, MY!C3:G10, ly, LY!C3:G10, lm, LAMBDA(a, ARRAYTOTEXT(UNIQUE(a, TRUE))),
d, IFNA(TEXTSPLIT(TEXTJOIN(";",, UNIQUE(TOCOL(VSTACK("EY, " & IF(ey = "", NA(), BYROW(ey, lm)),
IF(my = "", NA(), "MY, " & BYROW(my, lm)), IF(ly = "", NA(), "LY, " & BYROW(ly, lm))), 3))), ", ", ";", TRUE), ""), dr, DROP(d,, 3), 
chc, CHOOSECOLS(d, 3), SUBSTITUTE(PIVOTBY(CHOOSECOLS(d, 2),
BYROW(chc, LAMBDA(a, XLOOKUP(a, {"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"}, {1;2;3;4;5;6;7})))
 & "-" & chc, CHOOSECOLS(d, 1) & BYROW(IF(dr = "", dr, " - " & dr), LAMBDA(a, TEXTJOIN("",, a))), ARRAYTOTEXT,, 0,, 0), " -  - ", ""))

 

I know the formula is a bit big, but I hope it works.

 

Regards,

IlirU

Resources