+ Reply to Thread
Results 1 to 5 of 5

Array formula to return multiple values based on various (OR) criteria

  1. #1
    Forum Contributor
    Join Date
    07-19-2010
    Location
    Huddersfield, UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    167

    Array formula to return multiple values based on various (OR) criteria

    Ok, so I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

    My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

    I also have a list of engineers, with a column for their home postcode.

    I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered.

    So far I have this, which finds me all the engineers for just one postcode area.

    Please Login or Register  to view this content.
    So along with the unlined bit above, I want to add in if(or(list = G2, list = G3, list = G4....) etc. The list in G expands down from G2 to about G15, depending on the amount of postcodes that are returned.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Array formula to return multiple values based on various (OR) criteria

    Hi,

    Unfortunately, without seeing the actual workbook (or at least a mocked-up, dummy version which llustrates the same query) it will be quite difficult to help you.

    Regards
    Click * below if this answer helped

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

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Array formula to return multiple values based on various (OR) criteria

    I don't think you can use things like AND()/OR() in array formulas, maybe try something like:
    =IF(SUM(IF(list=G2:G15,1,0))>0,true,false)
    instead

  4. #4
    Forum Contributor
    Join Date
    07-19-2010
    Location
    Huddersfield, UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    167

    Re: Array formula to return multiple values based on various (OR) criteria

    Attached dummy file. I know the format is a bit messy - for now I just want to get it to work before I make it presentable.

    Postcode finder.xlsx

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Array formula to return multiple values based on various (OR) criteria

    Hi,

    Change your array formula in B6 to:

    =IFERROR(INDEX('Engineer Data & Referencing'!$A$2:$A$312,SMALL(IF(ISNUMBER(MATCH('Engineer Data & Referencing'!$D$2:$D$312,$G$2:$G$10,0)),ROW('Engineer Data & Referencing'!$A$2:$A$312)-MIN(ROW('Engineer Data & Referencing'!$A$2:$A$312))+1),ROWS($1:1))),"")

    Regards

+ 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] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  2. [SOLVED] ARRAY Formula to return multiple values
    By Ricardo Mass in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-01-2013, 01:55 PM
  3. Lookup/ Return and concatenate values based upon multiple criteria
    By steveboise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2013, 10:43 AM
  4. Return array based on multiple criteria
    By kmacd in forum Excel General
    Replies: 3
    Last Post: 01-05-2011, 09:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1