+ Reply to Thread
Results 1 to 11 of 11

Problems using Index/Match to pull multiple occurrences of "Match" criteria

  1. #1
    Registered User
    Join Date
    07-18-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    48

    Problems using Index/Match to pull multiple occurrences of "Match" criteria

    Good Morning,

    I am attempting to use the Index/Match to match each instance of "R&D" that is mentioned in Column B (B61:B100) (R&D Summary Tab), which have been pulled from the 'January Tab', and pull its subsequent "Description", "Date" and "$$" into the top 1/2 of the the same form --the "January Section" of the R&D Summary tab. The problem that I am running into, as you might imagine, is that the Index/Match formula is only matching the 1st instance of the "R&D" and not moving on the subsequent matches in the search array.

    I know that I most likely need to use some type of ROW function to help it determine when to move on, however, I am relatively new with excel, so I am struggling with the right combination. I know the solution is not difficult, but any help would be greatly appreciated.

    I have attached a sample spreadsheet for any possible assistance.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-18-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    Anyone have any recommendations/solutions? Any help would be be greatly appreciated!

    Thanks!

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    Since your Column Headers are not unique I am unable to provide single cell formula to which can rotate and get the results.

    All the below are array formula's, so requires Ctrl+Shift+Enter.

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


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


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


    Drag it down


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    07-18-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    Thank you Sixthsense! That works great! I really appreciate your help!

    If you don't mind, could you explain how/why you used the ROW and -60 and what the ,ROW (A1) does at the end of the formula?

    What could I have done to make the Column Headers Unique?

    Thank you again!

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    In cell A60 enter as Date

    In A4 cell - array formula - requires Ctrl+Shift+Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag it to right and down

    Please use Evaluate Formula for better understanding

  6. #6
    Registered User
    Join Date
    07-18-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    Sixthsense,

    I was wondering if you might be able to assist me one more time? I have to run a similar Index/Match as you just assisted me with, but with a different spreadsheet layout, and I am having an issue with working past the duplicates again. I am attempting to modify the formula you helped me with, but I am missing something. Do I need to re-post with a new question?

    Thanks again for your help.

    Bdavis

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    Please continue with this thread

  8. #8
    Registered User
    Join Date
    07-18-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    Thanks Sixthsense! I really appreciate the help!

    I attached a sample worksheet of what I am attempting where I am attempting to put the data now.

    I am looking at creating a 3 descending lists (Top 3 Only) from the table on the Left of the spreadsheet (A:H) to the "Dashboard View" on the Right (J1:R12).
    I want to sort the top 3 within each section found on the left "Department" (B6:B23), "Body Part" (B24:B38), "MOI" (B39:B50). This data will be sorted in column L.
    "Department" (L3:L5), Body Part (L6:L8), MOI (L9:L11). I have used the following formula to sort the numbers in that column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    That part is working fine.

    I am running into issues again when I am attempting to sort the "match" to the figures in Column B. There are obviously duplicates in the column B, but the corresponding Department, Body Part, or MOI is Unique. I attempted to modify the formula you helped me with last time, but I am running into a few snags where it is not pulling the 2nd or 3rd instance of the "repeated value".... it is only pulling the 1st.
    I would like to pull the corresponding data in Column D that is associated with the numbers in Column B.
    I have been attempting to use the following formula to accomplish this (I began to input the formula into Column K3:K5, K6:K8, and K9:K11 as you will see). I had the following formula variations in each of the following cells in column K (dragging down) with CSE:
    K3:K5
    Formula: copy to clipboard
    Please Login or Register  to view this content.


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


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


    I may be trying to use a formula that is not appropriate for this dataset, but I am still having significant issues working past the duplicate portions of these search issues.

    I am assuming I am missing a simple step to get this to work correctly, but I may be thinking incorrectly as well!

    Thank you again for your help!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-18-2012
    Location
    Hyderabad, Pakistan
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    I have various workbooks (sheets) containing same values therein but i can not trace similar entries in the absence of appropriate formula. Please guide me that how can i find similar entries in different workbooks(excel sheets). I am in urgent need of guidance because my annual promotion is subjected to task assigned to me. I will request an urgent and instant favour.

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

  11. #11
    Registered User
    Join Date
    07-18-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Problems using Index/Match to pull multiple occurrences of "Match" criteria

    I also tried the following formula to navigate through the duplicates, but I continuing having an issue collecting the correct answer for the 3rd result in Ranking.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    )

    This one is much longer, but a form of this has worked for me in the past, I think my "ROW" determination may be incorrect, but I can't figure out why.

    Thanks for your help.

+ 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