Tired of Excel's VLOOKUP? Use SQL queries on Excel ranges!

If you have the free xlwings Excel add-in installed, you can query your data using standard SQL statements, like this:

Overview

1. What is xlwings?
2. The sql formula
3. Behind the scenes

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 INDEX/MATCH over 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 a, 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.