+ Reply to Thread
Results 1 to 51 of 51

Index/Match not working is Excel Office 365?

  1. #1
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Index/Match not working is Excel Office 365?

    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.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

    Yes, it should work!
    You can upload the file to inspect the error...maybe user error

  3. #3
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    It'sRe: Index/Match not working is Excel Office 365?

    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.

  4. #4
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    I keep getting the "SPILL" error.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    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.

    Is there any way around this? Why does Microsoft constantly pull stuff like this? It’s infuriating.
    What stuff? INDEX MATCH works just fine in 365 - I use it a lot.
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Index/Match not working is Excel Office 365?

    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.

  7. #7
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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.

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

    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

  9. #9
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    Quote Originally Posted by BMV View Post
    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.
    Yes even when used the most simple way possible. Some sort of setting like you mentioned makes much more sense.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    We need to see a workbook.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Index/Match not working is Excel Office 365?

    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


  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Index/Match not working is Excel Office 365?

    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.

  13. #13
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

    Quote Originally Posted by S Thibault View Post
    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.
    .
    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

  14. #14
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    With Excel 2010

    S2Vs3Ad.png

    With Excel O365

    aXHvcaN.jpg
    Attached Files Attached Files

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    In my Excel 365 (insider program):

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    1
    Number Number2 Code Index Match
    2
    901068 901068-FBA B08BHKL9XZ B08BHKL9XZ
    3
    901102 901102-FBA B0883VGVLF B0883VGVLF
    4
    810402 810402-FBA B082P66391 B082P66391
    5
    810401 810401-FBA B082P651DL B082P651DL
    6
    810415 810415-FBA B082P5LZGH B082P5LZGH
    7
    906104 906104-FBA B08114NR72 B08114NR72
    8
    906101 906101-FBA B08114GWKY B08114GWKY
    9
    906102 906102-FBA B08114CM3Y B08114CM3Y
    10
    906106 906106-FBA B081132RHL B081132RHL
    11
    906105 906105-FBA B07ZZZL4QC B07ZZZL4QC
    12
    906103 906103-FBA B07ZZND53J B07ZZND53J
    13
    905681 905681-FBA B07ZTY63QQ B07ZTY63QQ
    14
    905675 905675-FBA B07ZS6VZD7 B07ZS6VZD7
    15
    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))

  16. #16
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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!

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    Did you try what I suggested?

  18. #18
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

    As Ali suggested add @ as follows:

    =INDEX(Sheet3!C:C,MATCH(@Sheet2!A:A,Sheet3!A:A,0))

  19. #19
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    Quote Originally Posted by AliGW View Post
    Did you try what I suggested?
    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.

  20. #20
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

    If you don't want to use the @, use this and drag down:

    =INDEX(Sheet3!C:C,MATCH(A2,Sheet3!A:A,0))

  21. #21
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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!

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    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.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    Did you enter Belinda's suggestion with C+S+E?

  24. #24
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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!

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    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.

  26. #26
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Index/Match not working is Excel Office 365?

    No, it's because you are doing this:
    Formula: copy to clipboard
    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.

  27. #27
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Index/Match not working is Excel Office 365?

    To be fair, I would always have used belinda200's approach in post #20

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    Me, too.

  29. #29
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

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

  30. #30
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    Quote Originally Posted by TMS View Post
    To be fair, I would always have used belinda200's approach in post #20
    Even when you have 20K+ rows to drag down multiple times in multiple columns?

  31. #31
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    Quote Originally Posted by belinda200 View Post
    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))
    Good tip! I will check this out.

  32. #32
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    Use structured tables - the formula will copy itself down then.

  33. #33
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

    S Thibault - when you have 20k+ rows then all the more so

  34. #34
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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?

  35. #35
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    Quote Originally Posted by belinda200 View Post
    S Thibault - when you have 20+ rows then all the more so
    I would rather just double click than sit there staring at the screen as I drag rows down endlessly.

  36. #36
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    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.

  37. #37
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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!

  38. #38
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    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.

  39. #39
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Index/Match not working is Excel Office 365?

    Even when you have 20K+ rows to drag down multiple times in multiple columns?
    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.

  40. #40
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

    Quote Originally Posted by S Thibault View Post
    Good tip! I will check this out.
    let's say I learnt it the hard way

  41. #41
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index/Match not working is Excel Office 365?

    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....

  42. #42
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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.

  43. #43
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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.

  44. #44
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Index/Match not working is Excel Office 365?

    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.

  45. #45
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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.

  46. #46
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Index/Match not working is Excel Office 365?

    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.

  47. #47
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    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.

  48. #48
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Index/Match not working is Excel Office 365?

    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: copy to clipboard
    Please Login or Register  to view this content.


    This, though, uses full column references, without the @ sign:
    Formula: copy to clipboard
    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.

  49. #49
    Registered User
    Join Date
    03-07-2015
    Location
    New York
    MS-Off Ver
    O365
    Posts
    56

    Re: Index/Match not working is Excel Office 365?

    TMS, I get it now. Thank you very much for the thorough explanation and everyone else who helped. Appreciate it.

  50. #50
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Index/Match not working is Excel Office 365?

    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.

  51. #51
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Index/Match not working is Excel Office 365?

    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.

+ 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. Need help with Index and Match Ms Office 2016 Home and Bus.
    By landfla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2019, 01:14 PM
  2. [SOLVED] Excel - Simple Index Match - Return all Instances - not working
    By ironfelix717 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-30-2017, 10:59 AM
  3. [SOLVED] help index and match macro not working but formula working??
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2016, 06:37 AM
  4. Correct/Working (Index,Match) formula not working between cells
    By barnerd in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-11-2014, 01:20 PM
  5. [SOLVED] If isna with index match not working in excel 2003
    By ryan4646 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2013, 10:56 PM
  6. Replies: 2
    Last Post: 05-24-2013, 09:32 AM
  7. Index/Match is not working in Excel 2003
    By jdeumer in forum Excel General
    Replies: 11
    Last Post: 07-21-2009, 06:08 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