Feb 11 2021 07:31 AM
Hello, I have been using the INDEX formula for years. I use the formula to input data from one sheet into another matching on a common cell (eg, filling in department id's matching on employee id's) I recently acquired a new laptop for work and when I use the INDEX formula it keeps returning a #SPILL error. I logged in to my old laptop and opened the same spreadsheets and did the formula and it worked as expected, then I went into the new laptop and the same files and the formula will not work. I have spoken to a few coworkers and none of them could see any reason that this would occur. If anyone has any ideas/resolutions it would b greatly appreciated.
Thank you, Jelena
Feb 11 2021 07:44 AM
SolutionThat means you are on relatively modern Excel which supports dynamic arrays Dynamic array formulas and spilled array behavior - Office Support (microsoft.com)
Most probably you may correct your formula by adding =@INDEX(... , but to re-write it in more correct way excluding rely on silent implicit intersection which is default behavior in legacy Excel
Implicit intersection operator: @ - Office Support (microsoft.com)
Feb 11 2021 07:53 AM
THANK YOU!! I tried what you suggested and my formula now works. I appreciate your help with this issue.
Thank you, Jelena
Feb 11 2021 08:04 AM
@Jelena_Mihajlovic , you are welcome, glad to help
Feb 11 2021 07:44 AM
SolutionThat means you are on relatively modern Excel which supports dynamic arrays Dynamic array formulas and spilled array behavior - Office Support (microsoft.com)
Most probably you may correct your formula by adding =@INDEX(... , but to re-write it in more correct way excluding rely on silent implicit intersection which is default behavior in legacy Excel
Implicit intersection operator: @ - Office Support (microsoft.com)