Results 1 to 8 of 8

"VLOOKUP" in Access

This is a discussion on "VLOOKUP" in Access within the Microsoft Access forums, part of the Question Forums category; I'm trying to do what would be a VLOOKUP or HLOOKUP in Excel using Access - will DLOOKUP work? If ...

  1. #1
    New Member
    Join Date
    Feb 2009
    Posts
    4

    Default "VLOOKUP" in Access

    I'm trying to do what would be a VLOOKUP or HLOOKUP in Excel using Access - will DLOOKUP work? If so, I'm not quite sure how...

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default Re: "VLOOKUP" in Access

    WELCOME TO THE BOARD!

    Many times, you don't need to. Access is a relational database program, so many times all you need to do is link two tables together, and pull out the fields you need.

    If you do not think this is what you want or if you want specific help, please describe exactly what you are trying to do in detail, and provide some examples.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Feb 2009
    Posts
    4

    Default Re: "VLOOKUP" in Access

    Thanks for the quick reply! But no, my problem is a bit more detailed than that. In particular I am wanting to take a value, listed by catalog #, look up that value in a table and return another value in either the 2nd, 3rd, or 4th column, depending on one of three physical locations (each catalog # can have up to 3 locations, although the look-up value will be the same for all three). In addition, there is another field connected to the catalog # that shows what source each catalog has. The source is the decision maker between two similar tables. I can put all of these in one table, but I have no idea how to pull out the value for each catalog. One more thing, these will not be an exact match, but the values are in ascending order in the lookup table. In Excel, the formula would be something along the line of an IF statement with 2 VLOOKUPs nested in it.

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default Re: "VLOOKUP" in Access

    You still may be able to do this in a query linking the two tables, but the details you provided are still a bit murky.

    If you could actually provide a few simple example, listing the some data you have in each table, and show the output you are expecting, it would go a long way in helping us to give you an answer that will work for you.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Feb 2009
    Posts
    4

    Default Re: "VLOOKUP" in Access

    In Excel, the base file has 5 columns:
    Catalog Source Loc1 Loc2 Value
    123456 China 422
    673334 Brazil 1827
    etc.

    Loc1 and Loc2 are what I'm using the Vlookup to find.

    "China" lookup table is as follows:
    Amt Loc1 Loc2
    0 7.9 8.25
    301 6.7 7.12
    1001 5.2 6.1

    "Brazil" lookup table is similar, but with different values:
    Amt Loc1 Loc2
    0 5.25 5.89
    501 4.23 5.07
    1001 3.71 4.33
    3001 2.25 3.04

    In the 1st example, the Loc1 and Loc2 values returned would be 5.2 and 6.1 respectively (looking up the 422 units in the "China" table). In the 2nd case, the Loc1 and Loc2 values returned would be 3.71 and 4.33, respectively.

    I had thought about putting together a table or crosstab query that would show all possibilities for each item, then somehow choosing from that, but I'm not sure that's the best way. Something so easy in Excel should "translate" to Access without this much problem, shouldn't it?

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default Re: "VLOOKUP" in Access

    Even in Excel, in order to use VLOOKUP, you have to match on a field. But I do not see any linking field between your Base Table and your Lookup Tables.

    What exactly does your Excel VLOOKUP formula look like?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Feb 2009
    Posts
    4

    Default Re: "VLOOKUP" in Access

    =IF($C5="S",VLOOKUP($J5,Domestic,2,TRUE),VLOOKUP($J5,Offshore,2,TRUE))

    Where C5 is the source (1-letter code in this case), J5 is the Qty, "Domestic" and "Offshore" are the two table names (range names in Excel) - the "TRUE" at the end indicates that this in NOT an exact match, but a range lookup - i.e., it's taking the value and looking it up on the table to see which range it falls in (422 is >301 but <1001). The ",2," tells is which column to use when choosing the return value. I noticed that my examples didn't post quite the way I had typed them - it's hard to read, but Loc1 and Loc2 are blank in both cases. The 422 and (I can't remember the other #) are the lookup values.

    This works just fine in Excel - trying to move it to Access because it's part of a larger process that we would like to run overnight with no manual intervention.

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default Re: "VLOOKUP" in Access

    I am quite familiar with Excel and VLOOKUP (I made my living programming in Excel long before I got involved in Access).

    I am still a little perplexed by your Excel setup. So do you have a separate lookup table for each and every "Source" (China, Brazil, etc). It would probably work much better in Access if you had these "Sources" as one large table, and have in this table named "Source" that has these values (China, Brazil, etc). This would create a linking field you could use to connect your tables.

    To best utilize Access, it is important to understand how Access works. It is a relational database tool, which means you have various tables which are "connected" or "linked" by common field(s). If there is nothing in common to link them on, I don't think you will gain any advantage to doing it Access versus Excel. Also, Data Normalization is also an important factor that can come into play with Access. If your tables are not designed properly, you are probably going to create a lot of frustration for yourself. My first dabbling in Access was to create a billing database, and I spent months on it, only to have to go back and educate myself on Access and re-start it all over again.

    It may be best to take a step back here. If you already have something in Excel that works, but is not quite automated to the way you like, and you really aren't proficient in Access, you might be best to keep it in Excel and look for help with the automating piece.

    What is it that you would like it to do in Excel that it does not do now?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com