what is the access equivalent to excel's vlookup function

Posted on 2009-04-26
Last Modified: 2012-05-06
what is the verticle lookup function in Access?  In excel it would be =VLOOKUP("1 FOUR AP1", D1:H17, 2, FALSE)  to return the value in the second field to the right of the lookup field.  I would like to return data based on position since my query is pulling in rolling totals (i.e. the header is not alwasy the same (date) but is in the same position) based on sorting ascending or decending.
Question by:kgittinger
    LVL 3

    Accepted Solution

    use DLookup function
    DLookup(expr, domain, [criteria])

    Dim varX As Variant
    varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")

    Author Comment

    I have not been able to make Dlookup work as I understand that it returns the value from a specific "named" field. such as:
    =DLookUp ("CurrentValue","myTable","[LookupField]='1 FOUR AP1')
    so I thought there must be something else.  

    How does Dim VarX As Variant work?  I want to return the value this is 1 column to the right of the lookup field (this is a date so it changes) or the second and so on.

    Author Comment

    I am looking to populate based on date most recent, most recent less 1, etc.  

    Here is a picture of my query.

    I want the 1st lookup to be 4/2, the 2nd to be 4/1, the 3rd to be 3/31.  The query is date > tmax date -7 so the field names will change daily.

    I hope this helps
    LVL 3

    Expert Comment

    =DLookUp ("CurrentValue","myTable","[LookupField]='1 FOUR AP1' ")  missing " char on the end of the  string

    How does Dim VarX As Variant work?  - This variable can be any type of data

    Author Comment

    Thanks, I miss typed.  Do I need to post a related question to get back to my original concern?  How do I tell it to look to the field 1 position to the right or 3 positions to the right of my lookup field?  since the field name (in this case  4/2, 4/1, 3/31, 3/30) changes each day. or perhaps I cannot...
    LVL 3

    Assisted Solution

    you can use array
    for example:

    dim sFields() as string
    Dim VarX As Variant
    sFields=array("4/2", "4/1", "3/31", "3/30")
    ' sFields(2) = "3/31"
    VarX =DLookUp (sFields(2),"myTable","[LookupField]='1 FOUR AP1' ")

    Author Closing Comment

    Thanks. It seems I will need to post a related question as it looks as though I will continually have to define the fields as my dates will change daily, but the positions do not.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now