SOLVED

Excel #SPILL error with INDEX formula

New Contributor

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

3 Replies
Best Response confirmed by Jelena_Mihajlovic (New Contributor)
Solution

@Jelena_Mihajlovic 

That 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)

 

@Sergei Baklan 

THANK YOU!! I tried what you suggested and my formula now works.  I appreciate your help with this issue.  

Thank you, Jelena 

@Jelena_Mihajlovic , you are welcome, glad to help