+ Reply to Thread
Results 1 to 9 of 9

Index Match :I want to retrieve all data in one column based on criteria shown in picture.

  1. #1
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    21

    Index Match :I want to retrieve all data in one column based on criteria shown in picture.

    Screenshot (1).png

    I want output as the name of the employee.

    Input is training name(example 5 S training or kaizen) and period of training.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,403

    Re: Index Match :I want to retrieve all data in one column based on criteria shown in pict

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  3. #3
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    21

    Re: Index Match :I want to retrieve all data in one column based on criteria shown in pict

    Hi alansidman

    Thanks for guidance.I am attaching excel sheet.
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,403

    Re: Index Match :I want to retrieve all data in one column based on criteria shown in pict

    Here is a VBA function. Note however that your criteria and lookups must be exactly the same. No spaces in one and not the other. No capitalizations in one and not the other. Case sensitive. Attention to detail is very important.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Index Match :I want to retrieve all data in one column based on criteria shown in pict

    Non VBA solution using CSE formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    but

    as above, the inputs and headers need to be controlled
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,748

    Re: Index Match :I want to retrieve all data in one column based on criteria shown in pict

    humding, you should know better!!
    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    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

  7. #7
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,244

    Re: Index Match :I want to retrieve all data in one column based on criteria shown in pict

    Put this on D15 and ENTER as ARRAY FORMULA then copied down :

    =IFERROR(INDEX($B$4:$B$6,SMALL(IF(INDEX($F$4:$H$6,0,MATCH($E$11,$F$3:$H$3,0))=$D$11,ROW($B$4:$B$6)-ROW($B$4)+1),ROWS($A$1:A1))),"")
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Index Match :I want to retrieve all data in one column based on criteria shown in pict

    sorry ... getting forgetful in my old age

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,748

    Re: Index Match :I want to retrieve all data in one column based on criteria shown in pict

    Quote Originally Posted by humdingaling View Post
    sorry ... getting forgetful in my old age
    Been there, got the mug and cap - I think

+ 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] Filter and extract list of data based on criteria using INDEX MATCH
    By brake in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-13-2018, 08:30 AM
  2. [SOLVED] complex if with index & match formula based on criteria in a column to extract data
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2018, 01:06 PM
  3. Need to retrieve multiple row items given two criteria (Index, Match, Row)
    By tonyridino in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-13-2015, 04:02 PM
  4. [SOLVED] Retrieve data using index-match formula
    By mso3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-11-2015, 09:17 PM
  5. [SOLVED] Using the MATCH-INDEX function to retrieve data.
    By APosada2 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-11-2015, 08:02 AM
  6. Replies: 7
    Last Post: 06-27-2014, 02:26 AM
  7. Replies: 10
    Last Post: 08-19-2013, 11:33 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