I use index match frequently and my formulas work perfectly fine in every version of excel until I moved to a new company that uses 365 (this was embarrassing).
I do not want to use x lookup. It actually has limitations that make it unusable for me.
Is there any way around this? Why does Microsoft constantly pull stuff like this? It’s infuriating.
Yes, it should work!
You can upload the file to inspect the error...maybe user error
It's not user error. I've used index/match over 10 thousand times. As I said. I can open the same file in any other version of excel and it works fine.
I keep getting the "SPILL" error.
Try entering the formula as an array with CTRL+SHIFT+ENTER. Failing that, attach the file here. It will be user error, although probably more an oversight than a mistake.
What stuff? INDEX MATCH works just fine in 365 - I use it a lot.Is there any way around this? Why does Microsoft constantly pull stuff like this? It’s infuriating.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Do you have error even you created small example? Probably you use some thing that depend on regional settings, display Language and it is different from you use before.
Sure I will post the file later. When I have access. Why would it work when I open in in any other version of excel if it was user error or oversight? That makes no sense.
I did try crtl shift enter and that did not work.
Spill could mean that something stands in the way of your range
It must be user error, not related to Ofiice version....you can upload the file for review
Yes even when used the most simple way possible. Some sort of setting like you mentioned makes much more sense.Originally Posted by BMV
We need to see a workbook.
It sounds as though the way you have structured your Index/Match is being interpreted as resulting an array output/result. If there is anything in the way of where Excel wants to put that array, you will get a #SPILL error.
Can you post the formula, ideally in a sample workbook?
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
It WILL be related to the version of Excel in that Spill will try to present you with an array of returned data whereas earlier versions (of Excel (without Spill)) will have ignored elements of your formula that it didn’t need.
It does make sense, since office 365 added the spill option, instead of dragging the formula along - you need to put the formula only in the 1st cell and it "spills" to the rest of the cells automatically.
#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid
#Spill might happen in the following cases:
Spill range isn't blank
Indeterminate size
Extends beyond the worksheet's edge
Table formula
Out of memory
Spill into merged cells
Unrecognized/Fallback
https://support.microsoft.com/en-us/...2-ef9cc9ad4023
With Excel 2010
S2Vs3Ad.png
With Excel O365
aXHvcaN.jpg
In my Excel 365 (insider program):
Excel 2016 (Windows) 32 bit
Number Number2 Code Index Match 901068 901068-FBA B08BHKL9XZ B08BHKL9XZ 901102 901102-FBA B0883VGVLF B0883VGVLF 810402 810402-FBA B082P66391 B082P66391 810401 810401-FBA B082P651DL B082P651DL 810415 810415-FBA B082P5LZGH B082P5LZGH 906104 906104-FBA B08114NR72 B08114NR72 906101 906101-FBA B08114GWKY B08114GWKY 906102 906102-FBA B08114CM3Y B08114CM3Y 906106 906106-FBA B081132RHL B081132RHL 906105 906105-FBA B07ZZZL4QC B07ZZZL4QC 906103 906103-FBA B07ZZND53J B07ZZND53J 905681 905681-FBA B07ZTY63QQ B07ZTY63QQ 905675 905675-FBA B07ZS6VZD7 B07ZS6VZD7 905672 905672-FBA B07ZS6FR3C B07ZS6FR3C
Sheet: Sheet2
No #SPILL! error.
Try adding the @ symbol:
=INDEX(Sheet3!C:C,MATCH(@Sheet2!A:A,Sheet3!A:A,0))
Okay, so there must be a problem in settings somewhere as someone else had suggested. This proves there is no user error as some have suggested.
Last edited by AliGW; 10-04-2020 at 12:32 PM. Reason: PLEASE don't quote unnecessarily!
Did you try what I suggested?
As Ali suggested add @ as follows:
=INDEX(Sheet3!C:C,MATCH(@Sheet2!A:A,Sheet3!A:A,0))
With the @ symbol?
That seems to work. I don't know why using a NEW "improved" version of software would force you to do more work. I don't know what the @ symbol is or why it works when used in this instance. I want to share my files with people who use different versions of excel and might not be familiar with that. Am I forced to use this from now on?
Last edited by S Thibault; 10-04-2020 at 12:39 PM.
If you don't want to use the @, use this and drag down:
=INDEX(Sheet3!C:C,MATCH(A2,Sheet3!A:A,0))
Dragging still creates spill.... and also not the best when you are working with more than a few thousand rows, which is very common for me
Last edited by AliGW; 10-04-2020 at 12:43 PM. Reason: PLEASE don't quote unnecessarily!
The thing is, Excel added it when I opened your file. It may be that the insider version I have is coping with these things better and not causing the #SPILL! error.
I don't think you'll have a problem with backwards compatibility with the @ symbol, but you'll need to test that theory.
Did you enter Belinda's suggestion with C+S+E?
Yes, I addressed this in post #7. I still get spill
Last edited by AliGW; 10-04-2020 at 12:48 PM. Reason: PLEASE don't quote unnecessarily!
I was referring to post #20.
Administrative Note:
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
No, it's because you are doing this:Formula:
Please Login or Register to view this content.
Now Excel (with Spill) only needs you to put a formula like that in the top cell and it does the rest for you. However, when you have copied the formula down, Excel can't "spill" the formula down into cells because there's already something there.
To be fair, I would always have used belinda200's approach in post #20
Me, too.
And since some of the values searched might be approached as numbers and some will not - I would convert them all to text within the formula like that to make sure nothing is missed:
=INDEX(Sheet3!C:C,MATCH(TEXT(A2,0),TEXT(Sheet3!A:A,0),0))
Even when you have 20K+ rows to drag down multiple times in multiple columns?
Good tip! I will check this out.
Use structured tables - the formula will copy itself down then.
S Thibault - when you have 20k+ rows then all the more so
I guess I can live with using the @ symbol. It looks like it is omitted if I open the file in 2010 and it adds it back if I reopen again in O365. It is a minor unexpected annoyance that really threw me off at work.
Can someone explain how/why this works in the newer O365 version?
I would rather just double click than sit there staring at the screen as I drag rows down endlessly.
It forces Excel to look at just the necessary row's entry. The @ symbol is used in structured tables in the same way - # for single cell reference, without for full column reference.
I use structured tables these days in large datasets.
https://support.microsoft.com/en-us/...3-c8ae6d2b276e
Last edited by AliGW; 10-04-2020 at 01:08 PM.
Ahh, I like tables too, but I cannot ALWAYS use them (various reasons)
Last edited by AliGW; 10-04-2020 at 01:11 PM. Reason: PLEASE don't quote unnecessarily!
I have asked you NOT to quote unnecessarily - please stop doing so!!!
What are the reasons for your not being able to use tables? I used to think I couldn't in some circumstances, but have found I can.
You don't have to drag down 20,000 rows, you can use AutoFill ... put the formula in the top cell and double click the cross hairs on the bottom right of the cell.Even when you have 20K+ rows to drag down multiple times in multiple columns?
let's say I learnt it the hard way
TMS - what is an Autofill? How do you use that? you mean the double-click ? If you have a blank cell in the middle it will stop there....
Yah, hes/she's just talking about the double click. I think there was a little bit of confusion about what we were talking about.
I am fine with the additional requirement of the @ symbol moving forward, but I am a little concerned it may add an extra layer of confusion when working with more complex nested forumlas.
Excel will add it if it's needed in more complex formulae when you open the workbooks in 365, but you would be far better off not using full column references where they are not needed, as they can slow down larger datasets considerably.
Unfortunately, I almost always have to use full column sets for the type of work that I do. I run off 64GB ram. For whatever the reason.. It does not automatically add it for me. I wish it did. I would like to find out why not.
MATCH(Sheet2!A:A,Sheet3!A:A,0) - i am not sure but it is wrong idea. Even you have huge RAM the processor resources is limited.
Could you explain more detail? This was just a quick example to address and explain the issue easily. It doesn't matter what way I search.
OK, Excel 365 will automatically put the @ sign in for you "when it feels it needs it". Hence, you can put this into cell D2 and it will SPILL down as far as the end of the data:Formula:Please Login or Register to view this content.
This, though, uses full column references, without the @ sign:Formula:Please Login or Register to view this content.
And the problem with that is that it SPILLS down the entire column ... hence IT MUST GO in row 1. That's why you get the SPILL error. Simply because you, not unreasonably, put the formula in row 2 and it doesn't have enough cells. However, the other problem with that is that you will get #N/A for the rest of the column after the data area. You could solve that with IFERROR but you don't really want a column full of a formula that it doesn't need.
Yes, AutoFill, as I described is the Double Click on the Cross-Hairs at the bottom right of the cell with the formula. And yes, if you hit a blank cell, it will stop. The sample data in this example does not appear to have blank cells, so it may not be an issue.
There are, of course, workarounds. You could, for example, put the "simple" formula in row 2, copy it, Shift and select the cell in the last row of data, and paste the formula.
Anyway, we understand the problem. We know how and why it happens. We/you have alternatives.
And I have learned some things
1) A formula that matches a full column of data against another column of data produces an array of results (automatically).
2) Excel 365 handles that scenario differently
3) How and why you get SPILL errors
4) Alternative ways to get the same result.
Lastly, 5) for all that, I would probably still go with the "simple" formula as I suspect it has the least performance hit and least backward/forward compatibility issues.
TMS, I get it now. Thank you very much for the thorough explanation and everyone else who helped. Appreciate it.
You're welcome.
It was an interesting question ... and I learned a few things too, thanks to belinda200 and AliGW.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Last edited by TMS; 10-04-2020 at 07:11 PM.
Should just add that belinda200's TEXT formula is very clever but it is hellish slow and I would not recommend it. Again, it is doing matrix manipulation with three full column reference ranges.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks