+ Reply to Thread
Results 1 to 5 of 5

Create a Vlookup function using VBA that can return the 2nd, 3rd.... nth value

  1. #1
    Registered User
    Join Date
    07-29-2020
    Location
    Sydney
    MS-Off Ver
    MICROSOFT 365 CONSUMER SUBSCRIPTION April 2020
    Posts
    11

    Create a Vlookup function using VBA that can return the 2nd, 3rd.... nth value

    Hi guys,

    I know there are a few ways to return the nth value that is found. Such as, using array formula (small, row, if combination), or add a help column (countif).

    But I am thinking it will make work lot easier if we can create a Vlookup function that can return the 2nd, 3rd.... nth value list (shown on columns on the right).

    Here is what I want to achieve:

    Source data:
    A 1
    B 2
    C 3
    B 4
    C 5
    D 6

    Goal:
    A 1
    B 2 4
    C 3 5

    I tried the following but not completed:

    Please Login or Register  to view this content.

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Create a Vlookup function using VBA that can return the 2nd, 3rd.... nth value

    Hi MsRoooose,

    Try the below UDF ... It has 5 arguments
    1. Txt - Lookup value
    2. Rg - Lookup range
    3. Col - Column index
    4. Sepr - Seperator between your output values (optional) - Default is , (comma)
    5. CaseSen - Yes/No or 1/0 if you want case sensitive (optional) - Default is No


    Please Login or Register  to view this content.
    Last edited by nankw83; 08-02-2020 at 09:57 AM. Reason: Added description
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create a Vlookup function using VBA that can return the 2nd, 3rd.... nth value

    Hi MsRoooose,

    Here are two ways that don't use VBA to get your answer. First is an index match and the second is a Pivot Table. If you sort your data the answer(s) will change. Can you give a better example of what you are given and want back to motivate a UDF to be created? See the attached for examples of non VBA answers. Transform Rows to Columns Index Match 2 columns.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    07-29-2020
    Location
    Sydney
    MS-Off Ver
    MICROSOFT 365 CONSUMER SUBSCRIPTION April 2020
    Posts
    11

    Re: Create a Vlookup function using VBA that can return the 2nd, 3rd.... nth value

    Thanks both Marvin and Nank!

    Both of your methods work well!
    Last edited by MsRoooose; 08-02-2020 at 07:46 PM.

  5. #5
    Registered User
    Join Date
    07-29-2020
    Location
    Sydney
    MS-Off Ver
    MICROSOFT 365 CONSUMER SUBSCRIPTION April 2020
    Posts
    11

    Re: Create a Vlookup function using VBA that can return the 2nd, 3rd.... nth value

    Quote Originally Posted by nankw83 View Post
    Hi MsRoooose,

    Try the below UDF ... It has 5 arguments
    1. Txt - Lookup value
    2. Rg - Lookup range
    3. Col - Column index
    4. Sepr - Seperator between your output values (optional) - Default is , (comma)
    5. CaseSen - Yes/No or 1/0 if you want case sensitive (optional) - Default is No


    Please Login or Register  to view this content.
    Hi Nank,

    Just wondering, for the "Sepr", instead of using a comma or a dash or so, is it possible to make it columns? So that I won't need to do "text to column" later on.

    Something like .offset(nth, rowNumber) would work?

    Thanks!
    Last edited by MsRoooose; 08-02-2020 at 07:55 PM.

+ 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: 06-28-2019, 12:20 PM
  2. Replies: 0
    Last Post: 09-08-2017, 09:31 AM
  3. Can we create the function for Vlookup in VBA
    By Ankit_Kumar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2015, 11:08 AM
  4. How to create a VLookup function?
    By jolyim in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-29-2012, 05:48 PM
  5. Worksheet function Vlookup and VLOOKUP return different results
    By zandero in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2010, 08:24 AM
  6. #N/A in VLookup - Can I use another function that will return 0?
    By Arla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-17-2006, 03:55 PM
  7. Create a function to return text if two logical functions are true
    By janeyt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2005, 05:06 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