+ Reply to Thread
Results 1 to 11 of 11

Help with dependent lists INDEX MATCH

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    Gainsborough, England
    MS-Off Ver
    Office 2016 & Office 365
    Posts
    87

    Help with dependent lists INDEX MATCH

    hello

    New to excel and formulation, as you will see by my questions and sample workbook, right here goes and please forgive bad terminology and spelling

    I have been experimenting with data validation, drop down lists, dependent lists all going well just got a little stuck using INDEX and MATCH formula.
    I may even be using the wrong formula for what I want to achieve...in the sample workbook I have column A drop down choose from a list of Machine Names,
    column B has list of products dependent to specific machine chosen, all data taken from other sheet...my problem is i want to automatically insert into
    column C run rate for the product chosen, I have it working but only for one machine which works with below formula, is there any way of modifying it to
    point to all the other machine run rates? sorry if I have made mistakes I hope people can understand what I mean when using sample workbook


    =INDEX(felixrate,MATCH(B2,Felix,0))




    Many thanks
    John
    Attached Files Attached Files
    Last edited by FDibbins; 10-19-2012 at 11:23 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with dependent lists INDEX MATCH

    looks like you almost had it, great job so far

    try changing your formula to...
    =INDEX(INDIRECT(A2&"rate"),MATCH(B2,INDIRECT(A2)),2)
    and then adjust your xxxrate ranges to include the data to the left as well. eg change felixrate to include B3:C6 etc

    let me know how you make out
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Help with dependent lists INDEX MATCH

    everything ok but your data validation is not correct.In LOg sheet product column
    Go to Data validation - list - in source area press F3 choose Felix Name range

  4. #4
    Registered User
    Join Date
    10-05-2012
    Location
    Gainsborough, England
    MS-Off Ver
    Office 2016 & Office 365
    Posts
    87

    Re: Help with dependent lists INDEX MATCH

    thanks FDibbins

    I am sure I will be back being a newbie....will try and do what you have suggested

  5. #5
    Registered User
    Join Date
    10-05-2012
    Location
    Gainsborough, England
    MS-Off Ver
    Office 2016 & Office 365
    Posts
    87

    Re: Help with dependent lists INDEX MATCH

    FDibbins

    Looks like your suggestion is working, all good so far, I dont think I would have got there by myself so many thanks for you help

  6. #6
    Registered User
    Join Date
    10-05-2012
    Location
    Gainsborough, England
    MS-Off Ver
    Office 2016 & Office 365
    Posts
    87

    Re: Help with dependent lists INDEX MATCH

    hi FDibbins

    Any chance you can take another look at the sample, thought it was working ok but it seems certain ones are not matching correctly, What is strange is that some are matching ok while others either show wrong rate or show an N/A and I can not work out why


    Thanks
    John
    Attached Files Attached Files

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with dependent lists INDEX MATCH

    sorry, i left off the ,0 in the match()

    =INDEX(INDIRECT(A2&"rate"),MATCH(B2,INDIRECT(A2),0),2)

    when i fixed that, for some reason it displayed the formula itself, and not the answer. no reformatting worked, so i copied the formula in edit mode from the edit line, clear-all'd C2 and then pasted the formula back into the formula bar, and copied down

  8. #8
    Registered User
    Join Date
    10-05-2012
    Location
    Gainsborough, England
    MS-Off Ver
    Office 2016 & Office 365
    Posts
    87

    Re: Help with dependent lists INDEX MATCH

    FDibbins

    Thank you very much that seems to have sorted it out....any chance you could break it down and explain what parts do/refer to what please

    =INDEX(INDIRECT(A2&"rate"),MATCH(B2,INDIRECT(A2),0),2)

    Once again thank you

    John

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with dependent lists INDEX MATCH

    sure.

    index works on index(range, row, column)
    indirect is used to take values or text and use convert them for use in a formula. your data ranges are the same as your machine name, except they have "rate" on the end. to i took your machine name and added "rate" to the end for range part
    =INDIRECT(A2&"rate")
    match returns a column or row number than can be used in a lookup
    so =MATCH(B2,INDIRECT(A2),0) finds what row B2 is in the range indicated in A2, the "0" is for an exact match
    so the entire formula works like this...

    =INDEX(INDIRECT(A2&"rate"),MATCH(B2,INDIRECT(A2),0),2)
    =index(sweeprate,2,2)

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with dependent lists INDEX MATCH

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  11. #11
    Registered User
    Join Date
    10-05-2012
    Location
    Gainsborough, England
    MS-Off Ver
    Office 2016 & Office 365
    Posts
    87

    Re: Help with dependent lists INDEX MATCH

    FDibbins

    Rep Done

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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