Engineers-Excel.com

Lookup against External Database



At work I had a requirement to find fields in an SQL database corresponding to data in Excel cells.  I looked for an Excel function to do this on the internet, but could not find one. I found this surprising as this must surely be a very common requirement.

While it is possible to access an SQL database data in Excel using the built in options for getting External Data, it is an overkill for a simple lookup to access a few pieces of data.

External data in Excel

To get around this, I wrote a function to perform LOOKUP against an external SQL database. It works in a similar manner to VLOOKUP, the parameters to be passed to it are the lookup value, the field name holding the lookup value, the table name containing the data and field to be looked up.

The connection string to access the database is also passed into the function. By changing the connection string, this function can be customised to lookup data against other databases (such as Microsoft Access).

For this function to work, a reference to "Microsoft ActiveX Data Objects x.x Library " must be made in VBA (under Tools Menu/References).

Macro References

The code for the function is as follows:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)

Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

SQLookup = "Error"

On Error GoTo Exit_Func

SQLookup = "No Conn"
Conn.Open ConnStr

Set RecSet = New Recordset

SQLookup = "Error"

RecSet.Open "SELECT " & FldName & " FROM " & TblName & " where " & LkUpFld & " like '" & LkUpValue & "'", ConnStr, , , adCmdText

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit_Func:

End Function

As an illustration of this function, let us assume that we have an SQL database named FRUITS containing a table tblFRUIT with the following entries:

Fruit Table

An example of the connection string to this database would be as follows:

"DRIVER=SQL Server;SERVER=SQLSvr;UID=ExcelUser;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=D-SQL01;DATABASE=Fruits"

A good reference for conneciton strings is this site.

To lookup for the Quantity of Grapes from this database, the function would be:
=SQLOOKUP("Grapes","Fruit","tblFRUIT","Quantity",A1). The cell A1 contains the connection string.

This function looks for the field "Grapes" under the field "Fruit" from the database table and returns the corresponding value under the field "Quantity", which is 31.

Like all Excel functions, the various parameters can come from other cells, see example below.

SQLookup Example

Note that this function only returns the first lookup value returned by the query in the code (same as VLOOKUP or HLOOKUP).

A word of caution though: This function is most suited to lookup for a few pieces of data from Excel and is not advisable to use this extensively on a sheet to perform many lookups. For querying large amounts of data from an External database, use Microsoft Query in Excel or the tools provided to access data from external databases.