[ModernMech]

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.

[JBiserkov]

I'm not sure what you mean by state and UI, but Excel definitely has a join-like functionality in the form of vlookup function.

https://support.office.com/en-in/article/VLOOKUP-function-0b...

[mistermann]

Now try doing a VLookup using more than one column.

Artificial limitations like this are one reason people rightfully hate Microsoft.

[tycho01]

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.

[mistermann]

> VBA also does SQL operations on Excel tables, but that's, well, worse.

Are you sure about that? Afaik it doesn't but I may be mistaken.

[tycho01]

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.

[mistermann]

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.

[toby]

This is totally true, of course... I just think that exploring examples that would be really hard with Excel would make the point better.