+ Reply to Thread
Results 1 to 8 of 8

Index Match combination in VBA

  1. #1
    Registered User
    Join Date
    03-02-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    15

    Index Match combination in VBA

    Dear all,

    I'm struggling with building a piece of VBA code which returns a productprice from a table.

    The User-defined-function should have one variable/argument, which is the productnumber.

    So in fact, in any cell in the entire worksheet can be searched for the price by using the user defined function: =Productprice("Apple")

    Then, the function should search for the product in my range called "Producttable" and return the corresponding price for the product.

    While this seems quite easy to achieve, i can't get it to work.

    I'd like to use the Index and Match combination instead of the Vlookup function, for known reasons.

    Attached i've got a sample file with the same names as above.

    Also, i've got the tableheaders given a rangename which is: "ProductTableHeaders"


    Who can help me in achieving my UDF?

    Thanks in advance!
    Best regards,
    A.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,593

    Re: Index Match combination in VBA

    I'll take a look and see what I can do for you
    Let me first see if I understand your table(s)
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,593

    Re: Index Match combination in VBA

    Okay, here's the UDF

    Please Login or Register  to view this content.
    Check to see if it works

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Index Match combination in VBA

    Like this

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In a standard code module
    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,593

    Re: Index Match combination in VBA

    BTW, I just checked but it will not work when you use it with focus on the worksheet,
    I'm modifying the code and will post modification later.
    Should be something else than find.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,593

    Re: Index Match combination in VBA

    Okay, here's working code:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-02-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    15

    Re: Index Match combination in VBA

    Thanks for all the replies!

    I've got it working with Keebellah's solution.

    Thanks!
    A.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,593

    Re: Index Match combination in VBA

    Good to hear it help solve the issue. Don't forget to mark it Solved (if you've not already done so)

+ 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. IF / INDEX-MATCH Combination
    By Joshua27 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-23-2015, 05:29 PM
  2. Combination of IF and INDEX/MATCH
    By flandophile in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2012, 09:52 AM
  3. Index and Match formula combination
    By anstasija in forum Excel General
    Replies: 3
    Last Post: 10-21-2011, 08:17 AM
  4. Index Match Combination for two variables
    By zhopa19 in forum Excel General
    Replies: 2
    Last Post: 06-21-2011, 03:07 PM
  5. combination of IF, INDEX/MATCH command
    By zombie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-23-2010, 10:49 AM
  6. Using INDEX/MATCH in combination with LARGE?
    By scoobz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-06-2008, 02:38 PM
  7. Combination Lookup - Match - Index
    By idrisb59 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-19-2008, 12:25 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