+ Reply to Thread
Results 1 to 11 of 11

Macro to vlookup multiple columns

  1. #1
    Registered User
    Join Date
    07-01-2019
    Location
    India
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Macro to vlookup multiple columns

    Hi,

    I need to populate almost 10 columns with information from another excel sheet. We usually do it using vlookup.
    I want to use a macro and automate it. SO that in just one click all the fields gets populated and saved.

    Table A where the information needs to be populated

    IP Location Metalcategory Teams Application Hosted Owner
    1.1.1.1
    1.1.1.2
    1.1.1.3
    1.1.1.2



    Table B which contains information which is required in table A

    IP Location Metal Category Teams Application Hosted Owner
    1.1.1.1 UK Iron Windows MS Outlook A
    1.1.1.2 USA Iron Unix Email B
    1.1.1.3 Australia Gold Unix Sharepoint A
    1.1.1.4 UK Silver Unix Sharepoint A
    1.1.1.5 India Bronze Windows Vmware C

    Regards,
    Keshav

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,487

    Re: Macro to vlookup multiple columns

    Still by VLOOKUP


    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    IP Location Metalcategory Teams Application Hosted
    Owner
    IP
    Location
    Metal Category
    Teams
    Application Hosted
    Owner
    2
    1.1.1.1 UK Iron Windows MS Outlook
    A
    1.1.1.1
    UK
    Iron
    Windows
    MS Outlook
    A
    3
    1.1.1.2 USA Iron Unix Email B
    0
    1.1.1.2
    USA
    Iron
    Unix
    Email B
    4
    1.1.1.3 Australia Gold Unix Sharepoint
    A
    1.1.1.3
    Australia
    Gold
    Unix
    Sharepoint
    A
    5
    1.1.1.2 USA Iron Unix Email B
    0
    1.1.1.4
    UK
    Silver
    Unix
    Sharepoint
    A
    6
    1.1.1.5
    India
    Bronze
    Windows
    Vmware
    C
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    1
    IP Location Metalcategory Teams Application Hosted
    Owner
    2
    1.1.1.1 =VLOOKUP($A2,$H$2:$M$6,COLUMN(),0) =VLOOKUP($A2,$H$2:$M$6,COLUMN(),0) =VLOOKUP($A2,$H$2:$M$6,COLUMN(),0) =VLOOKUP($A2,$H$2:$M$6,COLUMN(),0)
    =VLOOKUP($A2,$H$2:$M$6,COLUMN(),0)
    Sheet: Sheet1
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    07-01-2019
    Location
    India
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Re: Macro to vlookup multiple columns

    Thanks for the response But I am not sure how to use the formulas you have given.

    My requirement would be like:

    1. When we click on the button with the macro in it. It should ask to select the 2 files. one file where the data has to be populated and another where all the data is present.
    2. Once the data is populated, the file should be saved.

    Thanks
    Keshav

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,487

    Re: Macro to vlookup multiple columns

    Could you attached sample files, please?

  5. #5
    Registered User
    Join Date
    07-01-2019
    Location
    India
    MS-Off Ver
    MS Office 2016
    Posts
    5
    Quote Originally Posted by KOKOSEK View Post
    Could you attached sample files, please?
    Hi please find the sample files.
    Scan report - need to fill the blank columns
    Assets list - all IP details

    Also here the no. Of rows is less. But the actual report has thousands of rows and many columns.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,538

    Re: Macro to vlookup multiple columns

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here I will provide the link for you this time.)

    https://www.mrexcel.com/forum/excel-...e-columns.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Registered User
    Join Date
    07-01-2019
    Location
    India
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Re: Macro to vlookup multiple columns

    Sorry wasnt aware of it. I had to get tjhis work done quick and wasnt sure I was going to get a reply here. So had to post in different forum as well.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,538

    Re: Macro to vlookup multiple columns

    When you join a forum, it's usual to read the rules by which you will be expected to abide. Here they are for your convenience (see rule #3 which, by the way, is pretty much standard on all forums, including Mr Excel): https://www.excelforum.com/forum-rul...rum-rules.html

  9. #9
    Registered User
    Join Date
    07-01-2019
    Location
    India
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Re: Macro to vlookup multiple columns

    Ok. Can I know what should be done now?

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,538

    Re: Macro to vlookup multiple columns

    Please be patient.

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    11,793

    Re: Macro to vlookup multiple columns

    Not sure if this is acceptable, but you could always look at the thread you started on MrExcel

+ 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. [SOLVED] Vlookup Multiple Worksheets - Populate multiple columns at same time
    By seanodo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-08-2019, 11:18 AM
  2. Macro Vlookup multiple columns
    By cceze in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-27-2018, 06:20 PM
  3. vlookup to retun multiple column values against multiple columns
    By Kencosgrove in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-07-2016, 07:47 AM
  4. vlookup in macro for multiple columns
    By haperah in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2016, 01:38 PM
  5. macro for efficient vlookup with multiple lookup values in the columns to the left
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2015, 03:51 PM
  6. [SOLVED] VLookup - Single value lookup returning multiple records into multiple columns
    By kllovin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-07-2014, 05:14 AM
  7. Macro to vlookup across multiple columns
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-10-2013, 04:07 PM

Tags for this Thread

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