Help with transferring X Lookup into table

[cmstoren]

Hey - I've been using X-lookup to look for a particular cell value (student code) in a column on a second sheet, and if found, to return the data from 8 columns in the second sheet. The formula is =XLOOKUP(Sheet2!A20,Lookup_Data_Sem1!A:A,Lookup_Data_Sem1!F:T,0) and it works just fine. However, when I try and do exactly the same formula but in a table - it keeps giving me a #SPILL error. Can anyone please tell me what I'm doing wrong? Thanks in advance

 

[Alex Blakenburg]

You can't use a formula to return an array in a table ie returning multiple rows and / or columns. This is referred to as Spilling and a table can't handle it. Your return values are "Lookup_Data_Sem1!F:T" so you are returning multiple columns.

Change your formula to something like this and copy it to the right for the next 14 columns. The table should make it populate all rows.

=XLOOKUP([@ID],Lookup_Data_Sem1!$A:$A,Lookup_Data_Sem1!F:F,0)

 

[cmstoren]

Perfect - thank you so much for your help!

 

[Alex Blakenburg]

You're welcome.