+ Reply to Thread
Results 1 to 5 of 5

Spill Error with index match formula

  1. #1
    Registered User
    Join Date
    10-03-2019
    Location
    Bolton, England
    MS-Off Ver
    365
    Posts
    27

    Spill Error with index match formula

    Hi i use index match formulas all the time, they work great. However since updating my version of excel I now get a Spill error when trying to use the index match function.

    Can anyone help with this formula that was previously working but now produces this error?

    =INDEX('[RHS Snapshot 03.01.2020.xlsx]Live Sites'!$E:$E, MATCH(E:E,'[RHS Snapshot 03.01.2020.xlsx]Live Sites'!$B:$B,0))

    Kind Regards

    Danny

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Spill Error with index match formula

    I doubt it's a problem with Excel.

    Is that an array formula you're using? Have you tried restricting the E:E and B:B ranges. Never use whole column/row ranges unless you really do want to adress all 1m + rows and 16,384 columns. Either use a range that you know will never be exceeded or better still get into the habit of defining dynamic Range Names which automatically adjust to the size of data ranges.

    Have you checked the following MS article.

    https://support.office.com/en-gb/art...2-ef9cc9ad4023
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-03-2019
    Location
    Bolton, England
    MS-Off Ver
    365
    Posts
    27

    Re: Spill Error with index match formula

    Hi Richard,

    Well the formula's used to work fine up until a week ago and i updated my version of excel, I also came across this article saying the way these formulas work has changed. So im pretty sure it is a problem with Excel changing how lookup formula's work. Yes I've seen the article which prompted me to ask on here as before i updated the version the formulas stopped working and I keep getting this spill error that I have never gotten before. It even references these changes in the article you sent me:

    Before dynamic array capable Excel, Excel would only consider the value on the same row as the formula and ignore any others, as VLOOKUP expected only a single value. With the introduction of dynamic arrays, Excel considers all the values provided to the lookup_value. This means that if an entire column is given as the lookup_value argument, Excel will attempt to lookup all 1,048,576 values in the column. Once it's done, it will attempt to spill them to the grid, and will very likely hit the end of the grid resulting in a #SPILL! error.

  4. #4
    Registered User
    Join Date
    10-03-2019
    Location
    Bolton, England
    MS-Off Ver
    365
    Posts
    27

    Re: Spill Error with index match formula

    I have also tried limiting the range rather than using the whole column as a reference and that doesn't work either.

    Kind Regards

    Danny

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Spill Error with index match formula

    You need to change the portion in red to look at a single cell
    =INDEX('[RHS Snapshot 03.01.2020.xlsx]Live Sites'!$E:$E, MATCH(E:E,'[RHS Snapshot 03.01.2020.xlsx]Live Sites'!$B:$B,0))

    and as already said it would be better to limit the B:B range as well

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] sumif producing SPILL error
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2019, 10:44 AM
  2. #N/A Error with INDEX MATCH formula
    By manoj_b118 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-22-2015, 07:58 AM
  3. [SOLVED] Getting error N/A in index-match formula
    By mso3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2015, 09:12 PM
  4. [SOLVED] Index / match formula error
    By kevivu in forum Excel General
    Replies: 3
    Last Post: 07-12-2015, 06:24 AM
  5. [SOLVED] Getting error in index-match formula
    By mso3 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-11-2015, 10:17 AM
  6. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  7. [SOLVED] ERROR in my INDEX MATCH Formula
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2013, 10:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1