+ Reply to Thread
Results 1 to 6 of 6

return multiple results from one column based on cells in another column

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    Doha, Qatar
    MS-Off Ver
    Excel:mac 2011
    Posts
    3

    return multiple results from one column based on cells in another column

    I have a spreadsheet that lists names in the first column and then has columns to the right that say (for example) what work shift each person is on for that day. I'm looking for a formula that will look at one of the right hand columns for a specific shift (ie "LATE") and will then return all the names from the left column that correspond to that shift (hope this makes sense), this formula would then be applied to each column so that I end up with a list of names for each shift at the bottom of each days column.

    How do I return these multiple results? All cells contain text only and are not alphabetical.

    I've been looking for a solution for this for some time now, but none of the available options seems to work they either only work left to right (VLOOKUP), need to be alphabetical or require numerical data. I'm a total beginner when it comes to formulas so this is probably not helping...

    Thanks

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: return multiple results from one column based on cells in another column

    Ho and welcome to the forum

    It will better for all(first for you), if you upload a small sample workbook of your data.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    05-09-2012
    Location
    Doha, Qatar
    MS-Off Ver
    Excel:mac 2011
    Posts
    3

    Re: return multiple results from one column based on cells in another column

    CHOLLYFUNK EXAMPLE WORKBOOK.xlsxHi Fotis, thank you for the welcome - I'm looking forward to this community now my job needs this sort of stuff - it's all new to me...

    Here's an example workbook (hope I did that right) - should be much clearer than my muddled description

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: return multiple results from one column based on cells in another column

    Hi (I'm a Excel 2000 user) in the example attache I'm using array formulae

    Please Login or Register  to view this content.
    In Excel 2007 and later (your option) the formula can be dried as follows:

    Please Login or Register  to view this content.
    In the formulae row(a1) is the counter that helps the formula to get the first - Row(a1)- the second - Row(a2)- name you need to extract.

    All the formulae are confirmed with control+shift+enter.

    The formulae consider column A (names)=column I=column Q..

    Regards
    Attached Files Attached Files
    Last edited by canapone; 05-09-2012 at 11:41 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  5. #5
    Registered User
    Join Date
    05-09-2012
    Location
    Doha, Qatar
    MS-Off Ver
    Excel:mac 2011
    Posts
    3

    Re: return multiple results from one column based on cells in another column

    Holy Cow Canapone! That looks like it's working perfectly - thank you so much.

    Now I need to sit down and try to understand exactly what it's doing, but for now you've just made my job so much easier.

    Thanks again.

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: return multiple results from one column based on cells in another column

    Hi,

    later in the afternoon (in 6/7 hours) I'll have at disposal Excel 2011 too.

    I'll upload in my previous message the IFERROR version of the formulae (same results, more efficiency, less pollution).

    If you have problems in adjusting the formulae do not hesitate to share the matter to the Forum.

    Thanks for the kind feedback.

    Regards
    Last edited by canapone; 05-09-2012 at 05:22 AM.

+ 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