+ Reply to Thread
Results 1 to 6 of 6

Excel VLOOKUP reference an Access Database

  1. #1
    Forum Contributor
    Join Date
    09-21-2015
    Location
    nederland
    MS-Off Ver
    2021
    Posts
    202

    Excel VLOOKUP reference an Access Database

    Dear All,

    Can Excel VLOOKUP reference an Access Database? I have over a million records in Access (way too big for Excel), want to run VLOOKUP in Excel to match against these Access records. Reason I need to do this in Excel is because the users are not at all familar with Access and want to keep using Excel.
    See attachement access on zip file
    Thank you in advance
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by kirana2014; 02-27-2021 at 04:13 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Excel VLOOKUP reference an Access Database

    This would take some sophisticated VBA. You may want to consider bringing each table into Power Query and doing a left join to achieve your results. Your Excel table would consist only of the Customer Nr while your Access table would be as shown in your picture. Whenever you update/change your excel table, it would update the query when your click on Refresh All. If you would like a Demo, then you will need to upload a sample spreadsheet of Customer Nr. and a sample Access file containing the table you wished to query. We cannot manipulate data in pictures.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    09-21-2015
    Location
    nederland
    MS-Off Ver
    2021
    Posts
    202

    Re: Excel VLOOKUP reference an Access Database

    Dear Alan,

    I just upload the excel file and access database on (zip file)

    Please help me

    Thank You in advance

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Excel VLOOKUP reference an Access Database

    Here is the file back with the merged data

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-21-2015
    Location
    nederland
    MS-Off Ver
    2021
    Posts
    202

    Re: Excel VLOOKUP reference an Access Database

    Dear Alan,

    What we want is to look for a view customer from a million records in Access
    where i can add the vlookup

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Excel VLOOKUP reference an Access Database

    So you just want to look up a single customer? Is that my understanding? If it is something else, then you need to be more descriptive of what you are trying to do. There is no Vlookup in PQ. It is a case of mergers. If you are looking to find a single customer, I can set up a parameter query that will do that for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-31-2020, 10:34 PM
  2. List of Access database paths in Excel - Need to return Access version
    By Wedge120 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2019, 12:19 PM
  3. Replies: 16
    Last Post: 03-12-2019, 04:54 PM
  4. Replies: 2
    Last Post: 07-05-2013, 09:37 AM
  5. Replies: 2
    Last Post: 03-08-2006, 04:45 PM
  6. [SOLVED] vlookup and Access Database Table
    By finster26 in forum Excel General
    Replies: 1
    Last Post: 01-06-2006, 06:20 PM
  7. VLOOKUP in another workbook/Access database
    By Chris Carr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2005, 02:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1