Last updated on July 15, 2017
Tired of Excel's VLOOKUP? Use SQL queries on Excel ranges!
Posted by Felix Zumstein - Comments
If you have the free xlwings Excel add-in installed, you can query your data using standard SQL statements, like this:
What is xlwings?
xlwings is a Python package that allows us to write Python functions instead of VBA code. It recently added a full
blown Excel add-in that brought us “xlwings extensions”:
These are Excel functions that are available as soon as we install the add-in without any more setup steps required. The first built-in
extension is the “In-Excel SQL” extension that brings the power of SQL to your Excel spreadsheets.
Typically, you would use the
VLOOKUP formula to connect data from two distinct regions in Excel. Or maybe
you would prefer
VLOOKUP if you are an advanced user. However, the functionality of these formulas is limited and if you have
a lot of these functions, Excel can become quite slow. Often, we were banging our heads against the wall saying “this would be so
easy in SQL!”, well…now it is.
The sql formula
The usage of the sql formula is simple:
=sql("SQL Statement", table_a, table_b)
You simply have to refer to your tables as
b etc. in your SQL statement in the order you pass them in.
For more complex queries, it is advisable to write the query into a cell instead of directly in the formula which also allows
you to use multiple lines in your query (by using
Alt-Enter for a new line), e.g.
Behind the scenes
xlwings uses an in-memory sqlite database to provide the functionality, so refer to their homepage for further information regarding the sqlite dialect of SQL.