# Thread: Excel VLookup equivalent in Access

1. Registered User
Join Date
Feb 2004
Location
UK
Posts
246

## Unanswered: Excel VLookup equivalent in Access

Hi,
Does Access have an equivalent function Excel's VLookup?

If so it may answer a query I posted earlier

Regards
John

2. Registered User
Join Date
Jun 2004
Location
Florida, US
Posts
521
what VLookup does? there is a function called DLookup() but I'm not sure if it is what you are looking for.

3. Registered User
Join Date
Feb 2004
Location
UK
Posts
246
With Vlookup in Excel you can get the function to find a value from a range of values, eg
From To age
0.457 0.55 500
0.56 3.25 400
3.26 3.93 300
3.94 4.75 250

If I'm looking for an items age that has a value of 3.66 then the function will return 300 because age falls between the category of 3.26 to 3.93

Regards
John

4. Registered User
Join Date
Jun 2004
Location
Florida, US
Posts
521
yes dlookup what you are looking for. with dlookup you can find a value in a table based on a criteria.
Usage: Dlookup("LookupField", "YourTable", "Criteria")
The Function below finds age from YourTable with AValue given to function.

Code:
```Public Function FindAge(AValue as Double)
FindAge = DLookup("age", "YourTable", CStr(Avalue) & " Between [From] AND [To]"
End Function```
You can find more info in Access Help.
Last edited by ghozy; 06-09-04 at 15:16.

5. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
Here as an example I use in an Expression on a SetValue macro for doing insurance premiums

=DLookUp("[VTermNS]","1New1VTermM","[AgeNB]=[Forms]![SumPremAllCats1]![anb]")

Where VTermNS is a field in a table with an insurance rate, 1NewVTermM is the table and AgeNB is a field in the same table with age next birthdays.

[anb] is the age next birthday on the form SumPremAllCats1

6. Registered User
Join Date
Feb 2004
Location
UK
Posts
246
Hi Guys,
Thanks for the replies.

It looks like Dlookup will do what I need to to do, I've tried it in a form and it gives the values that I need correctly. So now its how I implement it.

The full syntax in my example is
=DLookUp("[Ageing]![Details]","ageing"," [Collection_notes2]![Days age] between [Ageing]![Age_lower_level] and [Ageing]![Age_upper_level] ")

Ideally (although against database design advice) I need this value in a field as it is used so much elsewhere in the database. The best way for me was if I could have the flexibility of Dlookup but implement it in a query that originally imports the data, How can I achieve this?

Regards
John

7. Registered User
Join Date
Jun 2004
Location
Florida, US
Posts
521
this SQL line should do same job. it only returns 1 result back. if you want to see all records fits into the criteria you should remove TOP 1 part from SQL code.

Code:
```
SELECT TOP 1 [Ageing]![Details]
FROM ageing
WHERE [Collection_notes2]![Days age] BETWEEN [Ageing]![Age_lower_level] AND [Ageing]![Age_upper_level]```
Last edited by ghozy; 06-10-04 at 10:53. Reason: typo in the SQL code

8. Registered User
Join Date
Feb 2004
Location
UK
Posts
246
Hi Ghozy,
I've slightly amended you suggestion and it works perfectly, thanks very much mate, its much appreciated

Regards
John

9. Registered User
Join Date
Feb 2013
Posts
1

## Re: equivalent of xl Lookup in access

Hi All,

Many thanks for your post relating to above. I am trying to achieve the same objective in a reporting database am trying to build. but am a bit stuck. My issue is around two tables

1. PAYGAgreement

2. qryLookupdata

I am tasked to create a calculated field named NewAgreement2 based on values in Newagreement field looking into PAYGAgreement table and returning the respective percentage.

Attached are extract copies of the two tables.

I have tried several methods. None seems working at the moment. I will appreciate if anyone can assist by pointing in the right direction.

Many thanks.

Afolabi

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•