Gotcha. Well, I've been wanting to throw this UDF together for a
while, and this just gave me an excuse. Paste the following code into
a regular module in your workbook.
Function databaseLookup(databaseFullName As String, _
databaseTableName As String, returnField As String, _
matchField As String, matchRange As Range)
Dim conn As Object, rs As Object, sql As String
sql = "SELECT [" & databaseTableName & "].[" & _
returnField & "] FROM `" & databaseFullName & _
"`.[" & databaseTableName & "] [" & _
databaseTableName & "] WHERE ([" & _
databaseTableName & "].[" & matchField & _
"]='" & matchRange.Text & "')"
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & databaseFullName
rs.Open sql, conn
databaseLookup = rs.Fields.Item(returnField)
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Function
The format of this function is:
=datbaseLookup(full path to database, full table name in that database
that you want to look in, the field to return if a match is found, the
field in that table to use as your match, the range to use as your
match criteria)

Then you can enter a formula such as below:
=databaseLookup("C:\database.mdb", "tbl", "return","match", A3)

In the above example, the function will look for the value in A3 in
the match field of the table named tbl in the C:\database.mdb
database. Once found, it will return the field named field within
that table.

As an option, you could store the name of the database in a cell and
reference it throughout your formulas.
In C1, place your database name. Then use the formula below to
reference that cell.
=databaseLookup($C$1, "tbl", "return","match", A3)

Shahid wrote:
The database is just over 200,000 records so pulling it into excel is a
pain....

" wrote:

On Oct 8, 10:26 am, Shahid wrote:
Hello,

Wanted to know if there was any way of extracting data from Access by using
a "Vlookup type" function in Excel.

I have IDs in excel but want to enrich that data with information held in
Access..

Is this possible?



I would imagine you would have to download a table from access to a
blank sheet then run a v-lookup against that table.