+ Reply to Thread
Results 1 to 22 of 22

Single Criteria Index Match with concatenated criteria NOT WORKING

  1. #1
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Single Criteria Index Match with concatenated criteria NOT WORKING

    Hi,

    I am having trouble with the following index match formula in VBA. Following is the code

    Please Login or Register  to view this content.

    The UDF refdate is a date vale in formate m/d/yyyy & discat is a text stirng.

    the z is showing the right concatinated value for x & y. but when it goes to MATCH formula , its says FALSE.

    and subsequently the index formula fails.

    I think it has something to do with variable defination.

    Any suggestion??

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Your code doesn't make sense to me...
    If you are saying "The UDF refdate", how are you using this?

    Obviously
    Please Login or Register  to view this content.
    It is not an UDF.

    and what do you expect from these 2 lines?
    Please Login or Register  to view this content.
    It may receive True/False.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    There are several syntax errors in your code. I'm not sure what you want to do. Especially the Milestone part This is my best guess.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 04-24-2017 at 03:06 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Sorry for the confusion! I am a newbie. (wrong term UDF, i was trying to refer to user defined variables...)

    Let me explain,

    I am trying to make an automated Gantt chart, having timeline (dates) in the top row and categories in the left column. What i am trying to accomplish with this code is to get the unique value for displayed milestone in a textbox.
    This is a just a portion of code for the Gantt chart.

    The milestones are displayed under 4 categories. Lets say its type 1.

    So when the target cell value will be 1, it will look for the corresponding row and column address in TIMELINE (top row) and CATEGORIES (left column), that is x and y. (working and showing the right values)

    with these address, refdate (which is a date), will be index from TIMELINE and discat (which is a string) from CATEGORIES. (working and showing the right values)

    These 2 values will serve as criteria for index match to lookup the right Milestone title.

    Now x and y are concatenated into z. (working and showing the right values)

    In excel i am using the formula " =INDEX( " Column Range for Milestone Names" ,MATCH(CONCATENATE( "Category", "Date" ), " Column Range for Milestone Dates" ,0))"

    and its working fine.

    which i am trying to translate into VBA so that in can work on the whole Gantt chart range.

    Criteria and Milestone are just breakup of the above mentioned formula. Criteria is the MATCH and Milestone is the overall INDEX part.

    I also tried using the worksheet function formula but whenever it comes to formula it will give different error, i.e most common type mismatch,

    That is why i was trying to use this formula structure by adding my defined variables at right place in the formula to get the same result.

    I have made a sample file but can't seem to find the attachment button.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Attach a sample workbook:

    Click on GO ADVANCED and then scroll down below the edit window to Manage Attachments to open the upload window.

  6. #6
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Please see the [attach] Sample Workbook.xlsb[attach]

    The main code is in Display Sheet. Module 1 is me trying with array formula to achieve the same result and Module 2 is the current try under discussion.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    hassanleo1987

    I have downloaded the file but couldn't open...
    It says 'File format or extension is not correct"...

  8. #8
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    The attachment is and xlsb file.

    I have uploaded the same workbook as xlsm.

    Sample Workbook.xlsm
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Same again...

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    I think the file has been encrypted by some sort of DRM system on the OP's network.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  11. #11
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Again uploaded as zip.

    It should work. Please see your security settings, both of them are VBA code enabled files. Latest office versions don't let you open such files directly.
    Attached Files Attached Files

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    And again, no. See my earlier post- those files have been encrypted by some sort of DRM system at your end.

  13. #13
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Sorry there is some kind of encryption problem. I will upload the sample file again from different system in a couple of minutes.

  14. #14
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    These attachments will work now.

    Please check.
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    OK this time.

  16. #16
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Any Suggestions?

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    What exactly are you trying to calculate? Your code doesn't make any sense to me in relation to your data as most attempts will fail to match.

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    I just gave up this...

    What is "Line 2" anyway?

  19. #19
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Quote Originally Posted by xlnitwit View Post
    What exactly are you trying to calculate? Your code doesn't make any sense to me in relation to your data as most attempts will fail to match.
    Have you checked the sample workbook files I uploaded? Please check if you haven't!

    The purpose of this code is to lookup the unique milestone name from DateTables sheet and displayed in the a Textbox (1 row offset on its location in Display sheet).
    This can be achieved in different ways. If you will check the Display sheet's code, I have displayed a shape at the location of each milestone and a text box using separate codes.

    Now what i need is code that can be called inside the textbox code where it is assigning the textbox a value. and that value can be looked up from DateTables sheet.

    The formulas that are working in excel sheet for this purpose are 2.

    1 - Multi criteria INDEX MATCH array formula =INDEX(colm_mstitle_rng,MATCH(1,(colm_dis_crt=AL27)*(colm_date1=AM26),0))
    2 - Single criteria INDEX MATCH formula where both criterias are concatenated to make a primary key for MATCH Loopup.
    =INDEX(colm_mstitle_rng,MATCH(CONCATENATE(AL27,AM26),DateTables!$I$5:$I$9,0))

    I tried to use the same formulas in VBA with small changes like the adding the target range and criteria as defined variables.
    But it is not working.

    It mostly returns type mismatch error in case of array formula which suggests the variables are not defined properly and
    in case of non array formula it returns FALSE due to format mismatch between lookup value and lookup column.

    I hope now you will get the full picture.

    Maybe there are better ways to do all of these 3 tasked in a single code, but since i am not very good with this, i have to break it up into solvable pieces.

    Please check the sample workkbook.xlsm or sample workbook.xlsb. Both are same just different format.

    Let me know if you have some suggestion!

    Thanks!

  20. #20
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Quote Originally Posted by jindon View Post
    I just gave up this...

    What is "Line 2" anyway?
    Really!!!!

  21. #21
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Quote Originally Posted by hassanleo1987 View Post
    Have you checked the sample workbook files I uploaded? Please check if you haven't!
    If I hadn't looked at them, I wouldn't be able to say that your code doesn't seem to relate to your data.

    The formulas that are working in excel sheet for this purpose are 2.

    1 - Multi criteria INDEX MATCH array formula =INDEX(colm_mstitle_rng,MATCH(1,(colm_dis_crt=AL27)*(colm_date1=AM26),0))
    2 - Single criteria INDEX MATCH formula where both criterias are concatenated to make a primary key for MATCH Loopup.
    =INDEX(colm_mstitle_rng,MATCH(CONCATENATE(AL27,AM26),DateTables!$I$5:$I$9,0))

    I tried to use the same formulas in VBA with small changes like the adding the target range and criteria as defined variables.
    But it is not working.
    For #2 you might use

    Please Login or Register  to view this content.
    as long as the discat and refdate values actually match your range.

  22. #22
    Registered User
    Join Date
    04-23-2017
    Location
    IRAQ
    MS-Off Ver
    2013
    Posts
    11

    Re: Single Criteria Index Match with concatenated criteria NOT WORKING

    Quote Originally Posted by xlnitwit View Post
    If I hadn't looked at them, I wouldn't be able to say that your code doesn't seem to relate to your data.



    For #2 you might use

    Please Login or Register  to view this content.
    as long as the discat and refdate values actually match your range.
    Thanks a lot . Its working great now.
    Really appreciate your help!

    Now the next portion is to get is code for all 4 cases.

    If you see the Addtextbox code in Display sheet which is
    Please Login or Register  to view this content.
    I want to run the milestone title code for each case's textbox value (currently Target.Value). What will be better way, a sub procedure or a function called when each any case value is true in the selected range?

    4 cases means for each case i will have to use different "msrng" column.

    Case 1 msrng = 'DateTables'!$I5$I9
    Case 2 msrng = 'DateTables'!$J5$J9
    Case 3 msrng = 'DateTables'!$K5$K9
    Case 4 msrng = 'DateTables'!$L5$L9
    Last edited by hassanleo1987; 04-26-2017 at 02:07 AM.

+ 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] Use Index/Match to return a value based on two criteria? Not working for me...
    By ralldredge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2015, 02:42 PM
  2. [SOLVED] Lookup with mutiple criteria - Vlookup, Match, index not working
    By PM1985 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-17-2014, 07:10 AM
  3. Return dates that match a single criteria (a countif criteria)
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2014, 12:43 PM
  4. [SOLVED] Index and Match for 4 Criteria - My 2 attempts are not working
    By denise001 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-05-2013, 01:55 PM
  5. [SOLVED] Index Match Multiple Criteria not working
    By garciapliz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-22-2013, 12:07 PM
  6. [SOLVED] INDEX/MATCH not working for multiple criteria
    By lukesanborn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-27-2012, 09:20 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