+ Reply to Thread
Results 1 to 2 of 2

VBA function to almost replicate xlookup but return array

  1. #1
    Registered User
    Join Date
    12-07-2022
    Location
    Indiana, USA
    MS-Off Ver
    2209
    Posts
    3

    Question VBA function to almost replicate xlookup but return array

    So I have two columns Material, and Process. The user selects a material and i need a function to look in a different sheet for all process matches to that material. Example would be a user picks Steel, and the the value returned is an array containing all processes that have the material steel. This shouldn't be too hard for someone better than I am at VBA coding, but I'm lost. Below is what ive tried and it just returns "0"

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,603

    Re: VBA function to almost replicate xlookup but return array

    Hi there,

    Take a look at the attached workbook and see if it gets you moving in the right direction. It uses the following code in a standard VBA CodeModule:

    Please Login or Register  to view this content.

    And the following code in the VBA CodeModule of the "Materials" worksheet:

    Please Login or Register  to view this content.
    The highlighted value may be altered to suit your requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

+ 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. XLOOKUP Equivalent when working with Data Model (to return array of results)
    By chris01395 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-27-2022, 10:00 AM
  2. Xlookup with dynamic return array
    By nmarcon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-01-2022, 11:26 AM
  3. Xlookup returns "-" but not the return array's value
    By immigrated4urjob in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-16-2021, 11:01 AM
  4. XLOOKUP with a dynamic return array
    By phrankndonna in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-04-2020, 09:33 AM
  5. Find max in array, return corresponding value in array, without max function
    By cshwkhelp in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-07-2017, 11:26 PM
  6. Replicate Array without Duplicates
    By STUARTXL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-21-2016, 02:46 PM
  7. UDF array function doesn't return array
    By taikalusikka in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2016, 08:06 AM

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