Because nobody in the Excel team uses Excel and it shows.
Disclaimer: Work at MS. We use Excel plenty, we just tend to do things differently than many other businesses as things that shouldn't be done in Excel we don't do in Excel (since we have other tooling available).
The challenge is that in reality, people are diverse and will use Excel as long as they can as they don't want to buy other software, lack skills to implement it differently, or for some other reason need to keep data in Excel.
Software like Excel has been around for so long and has so many edge cases in how it is used that the dev teams could spend 100% of their time adding features and still probably not touch some potentially large user base ones.
Top of my head, a few examples (in addition of the index/match):
1. the fx "Insert function" window, with a search feature that just doesn't work, a listbox that shows only 7 results and is not resizable (when the built-in functions alone is a huge list, plus all custom udf). Same thing for the Function Arguments window.
2. excel incapable of preserving the default for calculation mode (automatic/manual) between sessions
3. excel recalculating the whole sessions when opening a csv file even when calcs are on manual
4. terrible default formats (serious people don't want thick lines, 3d charts and all that crap)
5. Recent regression: can't copy a table from excel to powerpoint while preserving the format, formatting a table in powerpoint is a pain in the butt, default format are just bad
6. No way to link a powerpoint to a spreadsheet. Think all the bankers, consultants, engineers, who must present the result of some analysis, the data is in excel, the deliverable is a powerpoint deck, how do you link the two. Only ways I know are awefully manual and/or unstable and/or result in tables displayed as images in powerpoint
7. VBA not developped or improved since the late 90s (there was an interesting attempt at one point, VSTA, but it was canned)
I could go into more edge case things but these are mass usage features.
VSTA was not canned [0], but core Office apps never supported it. IIRC, the only Office app that ever used (uses?) VSTA instead of VBA is InfoPath. VSTA was more of a replacement for VBA as an offering for other people (VBA, as well as being used in office, was available for others to use in their apps), who didn't have Office’s backward compatibility constraints.
[0] VSTA 2019 SDK is available here: https://www.microsoft.com/en-us/download/details.aspx?id=583...
So no - some of your examples are very much edge cases, preference, or lack of understanding how MS works.
Example(s) off your list: item #4 - that's subjective and I am certain there are users that do want that feature. This same feedback probably applies to #3 also. Or not enough people care / complain about #2 and #3 to get them prioritized.
#5 and 6 is probably because people tend to think that MS is one unified company; it's more like little ones that group together to produce a product. The Excel and PowerPoint teams pre-Satya wouldn't have even talked on features outside of integration testing. That item also might be a PowerPoint issue, a Windows OS issue (for how the formatting gets preserved on copy) and not related to Excel at all.
The VBA item someone else has already addressed.
MS dogfoods heavily; it's just that MS doesn't represent their user base (hence the push for insiders - as insiders may represent the user base). I think most large companies (Google, Amazon, etc) that make software have that same issue (lack of user base representation internally).