what is the access equivalent to excel's vlookup function

Posted on 2009-04-26
Medium Priority
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
  • 4
  • 3

Accepted Solution

prachwal earned 1500 total points
ID: 24236001
use DLookup function
DLookup(expr, domain, [criteria])

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

Author Comment

ID: 24236112
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

ID: 24236133
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.


Expert Comment

ID: 24236154
=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

ID: 24236183
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...

Assisted Solution

prachwal earned 1500 total points
ID: 24236219
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

ID: 31574663
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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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.

850 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