DLookup for Excel

I had to do a couple of ad-hoc Excel jobs today and found that whilst Excel has a VLookup function for spreadsheet/ranges it doesn’t have one for databases.

It’s been a while since I touched VBA and Access but the DLookup function was quite useful so here it is for Excel. Read the warnings below before you use it!

Public Function DLookup(expression As String, domain As String, criteria As String, Optional connectionString As String)

    Dim con As ADODB.connection
    Set con = New ADODB.connection
    If IsMissing(connectionString) Then
        con.Open "Driver={SQL Server};Server=abc;Database=def;Trusted_Connection=Yes;"
    Else
        con.Open connectionString
    End If

    Dim rs As ADODB.Recordset
    Dim sql As String
    sql = "SELECT TOP 1 " + expression + " FROM " + domain + " WHERE " + criteria
    Set rs = con.Execute(sql)
    If Not rs.EOF Then DLookup = rs(0)
    rs.Close
    Set rs = Nothing

    con.Close
    Set con = Nothing

End Function

Copy and paste the code above into an Excel module and replace the “Driver=… with what you want to be your default connection.

Some examples of use:

To select the Name column from the Customers table where the numeric ID is held in column 2.

=DLookup("Name","Customers","ID=" & A2)

To return Forename and Surname columns joined together with a space from the Contacts table in the crm database using the postcode from column 5

=DLookup("Forname + ' ' + Surname","crm..Contact","PostCode='" & A5 & "'")

To return Description from a Products table held in another connection using a partial like match from column 1:

=DLookup("Description","Products","OrderCode LIKE '" & A1 & "%'", "DSN=SecondaryDSN")

I think you get the idea!

There are a few caveats you need to be aware of using this function:</p>
  • It only returns the first value of the first row
  • DLookup’s are not efficient opening a connection each time it is referenced. This could put a lot of strain on your server.
  • DLookup syntax is open-ended and has some attack vectors.

If that doesn’t scare you then give it a shot. As usual no warranty expressed or implied!

[)amien

3 responses

  1. Avatar for Brad

    As the function only returns one row you should force the query to only return one row using the TOP select constraint to minimise load on the server. Acheive this by modifying your sql assignment to read: sql = “SELECT TOP 1 “ + expression + “ FROM “ + domain + “ WHERE “ + criteria

    Brad – March 5th, 2008
  2. Avatar for Damien Guard

    Yes, that makes more sense. I’ve updated the function, thanks!

    Damien Guard March 5th, 2008
  3. Avatar for TxGeekGirl

    Thank you so much for posting. I am so used to writing sql statements in VB or Access and needed one for an Excel project this morning. I couldn’t get a multiple criteria VLOOKUP or an INDEX/MATCH to work with the data I had - but this was a gem.

    TxGeekGirl – April 17th, 2017