Using XLOOKUP against multiple sheets - Spill function not working as expected, but identical non-spill copied down formulas are working

Hello,

I am trying to utilise spill functions more effectively so that I don't have to have hundreds of lines of formulas as has been prevalent in our team in the past.

I have already written a spill function in B5# as follows:

=FILTER('Certification table'!A:A,(ISNUMBER(SEARCH(C2,'Certification table'!C:C)))+('Certification table'!C:C="All"),"")

This finds all certifications that someone from a given team (listed in cell C2) should have. This works as expected.

I then created a function in cell C5# as follows:

=XLOOKUP(B5#,FILTER('Advance report'!A:A,'Advance report'!E:E=$D$2),FILTER('Advance report'!M:M,'Advance report'!E:E=$D$2),
XLOOKUP(B5#,FILTER('Other Training (2)'!A:A,'Other Training (2)'!D:D=$D$2),FILTER('Other Training (2)'!G:G,'Other Training (2)'!D:D=$D$2),""))

This should be able to find the status of any certification against the listed person's email address, but because these are split across two sheets, it doesn't seem to be working as expected.

The "Advance report" sheet is pulled directly from any online training that someone has done, and the "Other Training" sheet has items typed in manually whenever a member of staff completes an in-person course with relevant certifications, so we can't combine the two sheets without duplicating data.

Relevant columns in "Advance Report": A: Certification Name E: Person's email address M: Status of Certification

Relevant columns in "Other Training": A: Certification Name D: Person's email address G: Status of Certification

I have written the following into cell D5:

=XLOOKUP(B5,FILTER('Advance report'!A:A,'Advance report'!E:E=$D$2),FILTER('Advance report'!M:M,'Advance report'!E:E=$D$2),
XLOOKUP(B5,FILTER('Other Training (2)'!A:A,'Other Training (2)'!D:D=$D$2),FILTER('Other Training (2)'!G:G,'Other Training (2)'!D:D=$D$2),""))

and then copied that formula down the column to cell D23. This formula is identical to the one in C5#, except it is using references to each the relevant cell in the range, (B5, B6 etc) rather than the whole spill range B5#.

As shown in the picture, this returns an extra certification that this member of staff has completed in row 22, as it comes from the "Other Training" sheet. This has not shown up in the spill version of the function for some reason.

I was quite excited to get into spill functions as our team had been using quite outdated formulas for a while before I joined, and they could become unwieldy when a function could return thousands of lines one day and then 10 the next, with those thousands of lines of formulae still taking time to calculate and making the file size massive! I'd like to know if it is possible to do something with this or if I need to revert back to the "old" way.

Please also let me know if there's anything else I could do to make this cleaner! I am used to writing VBA rather than excel functions.

I think using the BYROW function could work - seems to be ok for me in a quick draft I set up. Please try:

=BYROW(B5#, LAMBDA(array, XLOOKUP(array,FILTER('Advance report'!A:A,'Advance report'!E:E=$D$2),FILTER('Advance report'!M:M,'Advance report'!E:E=$D$2), XLOOKUP(array,FILTER('Other training (2)'!A:A,'Other training (2)'!D:D=$D$2),FILTER('Other training (2)'!G:G,'Other training (2)'!D:D=$D$2),""))))

This applies your formula to each row of the the B5# array independently, but outputs a dynamic range.