Recreating a Google Spreadsheets QUERY() in Excel

Question

I have a massive Excel Workbook that I use for tracking product titles and descriptions, and I'm trying to get it to generate .csv files for importing those products into eBay and my own website. I'm 99% of the way there, but I can't seem to find the Excel equivalent of Google Spreadsheet's QUERY() function, and there are two spots I need to use it:

1st, I need to populate a column in SheetB with the Product IDs in SheetA that have not been listed on the site. In Google Spreadsheets, I would do this with =query('SheetA'A:B,"select A where isblank(B)") but I can't for the life of me figure out the equivalent in Excel.

2nd, I need to take all the non-blank rows from four different sheets and put them together into one sheet.

There has to be something obvious I'm missing, but I'm missing it. Help me, magical internet people, you're my only hope!

Answer 1

The bad news is that Excel does not have an equivalent of QUERY()

The not quite so bad news is that you can roll your own.

Some possible ways:

1. Move your data to a database. You can do the whole thing in the database, or still use Excel as a front end (eg by using Get External Data to query the db, or build it all in VBA)
2. Use AutoFilters to query your data (this is not SQL queries...). There a plenty of examples on SO of this technique
3. Use Get External Data (in spite of the name, this can be self referencing) to query your data (this is SQL queries) See this answer for a starter