+ Reply to Thread
Results 1 to 5 of 5

lookup values that meet certain criteria

  1. #1
    Registered User
    Join Date
    09-22-2015
    Location
    250
    MS-Off Ver
    Office 2007
    Posts
    3

    lookup values that meet certain criteria

    I have a table of data responses to a survey. Gradually, the number of rows grows over time as the number of survey respondents grows. Below is a dummy table of responses for the first 16 people.
    Capture1.PNG

    I want to create a spreadsheet that automatically gives me the names of the survey respondents who meet certain criteria, as shown below. For example, I want a column with the names of everyone who selected "orange" as their answer to Question #1, a separate column for everyone who selected "green" as their answer to Question #2, and so forth (as shown below). Ideally, the names would be listed in alphabetical order but this is not absolutely required.
    Capture2.PNG

    Any suggestions?

    PS: Attached is an Excel file with the input data, as well as the desired output layout. dummy data.xlsx

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: lookup values that meet certain criteria

    I believe it can be done with an Array formula however, Im not too good at those (to be honest I hide when theyre mentioned but I am trying to get round that phobia!).

    Read up on Array formulas here > http://www.cpearson.com/excel/arrayformulas.aspx

    and I believe this with a bit of tweaking, will do what you want > http://thinketg.com/how-to-return-mu...ch-or-vlookup/
    If someone has helped you then please add to their Reputation

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,178

    Re: lookup values that meet certain criteria

    Try ...

    in G2 ...

    =IFERROR(INDEX($A$2:$A$16,SMALL(IF($B$2:$D$16=G$1,ROW($A$2:$A$16)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

    Enter with Ctrl + Shift +Enter

    Copy across and down
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-22-2015
    Location
    250
    MS-Off Ver
    Office 2007
    Posts
    3

    Re: lookup values that meet certain criteria

    Thank you ! This worked.

  5. #5
    Registered User
    Join Date
    09-22-2015
    Location
    250
    MS-Off Ver
    Office 2007
    Posts
    3

    Re: lookup values that meet certain criteria

    this worked, thank you

+ 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. Lookup return values if meet criteria?
    By lissamel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2015, 10:21 AM
  2. Display top five values that meet criteria
    By themob212 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2015, 02:29 PM
  3. how to add all values above row that meet multiple criteria criteria
    By skikio in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-18-2015, 09:40 AM
  4. [SOLVED] Lookup Values which meet two or more criteria and return multiple matches horizontally
    By josetmg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-03-2015, 01:16 AM
  5. LOOKUP Data across a range of cells to meet a specific criteria.
    By ads111ads in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-30-2014, 01:15 PM
  6. [SOLVED] Advanced Lookup to count cells in a column that meet criteria
    By shepherdc2814 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2013, 12:59 PM
  7. [SOLVED] Array Formula to Lookup and Return All Rows in Table that Meet Single Criteria
    By Torkel74 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2013, 10:41 PM

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