> If Facebook had an "export my friend list to Excel" button, then plenty of non-programmers could perform this task using existing tools.
The task was to send alerts whenever a friend was nearby. Excel is amazing for non-professional programmers, but it doesn't deal well with changing data, unless you want to click the "export my friend list to Excel" button every five minutes. There are tools that will act as real-time data sources in Excel but it's not a natural fit. If we did nothing but make "real-time Excel" it would still be incredibly useful for a lot of people.
An excel power-user is indistinguishable from a programmer. If you are an excel power-user, and don't think you are a programmer, learn javascript, http://eloquentjavascript.net/.
I've known many Excel power users who are technically minded and experts in their domains, but nevertheless are not programmers. (Edit: obviously they're programmers in the sense that they make the computer compute things, but not via a general-purpose language.) They have no interest in learning Python or JavaScript or even VBA—it doesn't fit with how they like to think. Instead, they lay out complex calculations in Excel using long chains of intermediate columns (zeros and ones and COUNTIF, anyone?).
There are definitely some symmetries there, but also differences. These users don't think so abstractly. They get their computation working on one set of numbers and then, if they need to reuse it, copy-paste and modify.
The only thing a super complex formula is missing is the notion of a for loop. In lieu of that, I used to pull a column down as many times as the loop needed to run. Then I discovered macros, then I discovered javascript.
Some spreadsheet users discover programming and take to it, which is great. Others have the opposite reaction—writing scripts doesn't fit how they like to think or work. It sounds like you are the first kind of user; the ones I was thinking of are the latter.
Excel is a very powerful tool, but it reaches a utility plateau very quickly when you start going outside its intended purpose of being a spreadsheet application. You're correct that a power-user can do pretty much anything in Excel, but when you start talking about actual database operations (like Join), state, and UI, then you're at the point where the tool is working against you.
The so-so way: use MATCH in one column to get the row numbers, INDEX in one column for each column you want to pull in to actually grab them using that row number.
Alternative: join the tables using Power Query so you could have it refresh and give you the combined version even after adding more columns.
I mean, point taken, but in case you were wondering, then yeah.
VBA also does SQL operations on Excel tables, but that's, well, worse.
Yeah, it could be done using the ADODB interface on data in the workbook itself rather than from some actual DB. See this link for an example: http://stackoverflow.com/a/26678696/1502035
That being said, when I encountered someone doing this I was pretty surprised as well.
Pretty wild - it would be nice if MS would put a little effort into their cash cows now and then so people wouldn't have to resort to such things though.
I read the tutorial and I am sort of struggling to see what benefit this would give someone who was already an Excel power-user.