+ Reply to Thread
Results 1 to 8 of 8

ARRAY Formula to return multiple values

  1. 11-01-2013, 11:44 AM #1
    Ricardo Mass
    Ricardo Mass is offline
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    ARRAY Formula to return multiple values

    Hi everybody,

    I am trying to find a formula which will act as a vlookup but returning multiple values.

    As per the attached sheet, I would like a formula in col G which looks up job titles in col F from col A, and returns all names matching that title in col B.

    I hope this makes sense, I have been at this for a while, trying to adapt formulae I have found online but to no avail. I am fairly comfortable with array formulae so that should be fine.

    Many thanks in advance.

    RichARRAY formula file.xlsx
    Reply With QuoteRegister To Reply
  2. 11-01-2013, 11:52 AM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: ARRAY Formula to return multiple values

    In G4, confirmed with CSE:

    =IFERROR(INDEX($B$1:$B$16, SMALL(IF($A$1:$A$16=F4, ROW($A$1:$A$16)), COUNTIF(F$1:F4, F4))), "")
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 11-01-2013, 11:53 AM #3
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: ARRAY Formula to return multiple values

    A Pivot Table is one way.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  4. 11-01-2013, 11:57 AM #4
    Ricardo Mass
    Ricardo Mass is offline
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: ARRAY Formula to return multiple values

    Thank you both, I am just testing these solutions with my data.

    Much appreciated.

    Rich
    Reply With QuoteRegister To Reply
  5. 11-01-2013, 12:05 PM #5
    Ricardo Mass
    Ricardo Mass is offline
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: ARRAY Formula to return multiple values

    Pivot table works perfectly, but I am working to a colleagues brief and the formula will fit in with their process a bit better.

    It was the COUNTIF bit that I was missing, thank you very much.
    Reply With QuoteRegister To Reply
  6. 11-01-2013, 12:32 PM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: ARRAY Formula to return multiple values

    Just a little-bit different

    An array formula confirm with Ctrl+Shift and Enter

    =IFERROR(INDIRECT("B"&SMALL(IF($A$4:$A$16=F16,ROW($A$4:$A$16)),IF(F15=F16,J15+1,1))),"")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  7. 11-01-2013, 12:55 PM #7
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: ARRAY Formula to return multiple values

    You're welcome.
    Reply With QuoteRegister To Reply
  8. 11-01-2013, 01:55 PM #8
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: ARRAY Formula to return multiple values

    Just goes to show that there is more than one way of solving most problems. I'm glad that you got a solution that works for you and your colleagues.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. add to array formula to only return unique values
    By jason892 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2013, 06:39 AM
  2. Array formula to return Max values or zero
    By Motox in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-17-2012, 09:18 PM
  3. Find a value in an array and return multiple values in an adjacent array
    By tonbra in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2012, 08:35 PM
  4. Array Lookup, Return/Sum Multiple Values
    By mre2000 in forum Excel General
    Replies: 3
    Last Post: 01-18-2010, 10:45 PM
  5. Formula / array to return multiple values
    By paulmag in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2008, 11:52 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1