Results 1 to 7 of 7

Thread: Expanding Array Formula to Include an OR option

  1. #1
    New Member
    Join Date
    Apr 2013
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Expanding Array Formula to Include an OR option

    hi,

    I have the following formula which works well for me when it captures criteria based on one cell value.

    Code:
    =IF(ISERROR(INDEX(employee_names,SMALL(IF(day_1=$A$11,ROW(day_1)),ROW(1:1))-1,1))," ",INDEX(employee_names,SMALL(IF(day_1=$A$11,ROW(day_1)),ROW(1:1))-1,1))

    I'm trying to expand it to so that the day_1 can check the criteria of more that one cell for example OR(day_1=$A$11,day_1=$A$12). When I do try the example previous I get all values in the employee names range rather than just the ones with the values required.

    Wonder is that possible or should I look at another way of populating the fields with the formula? I'm trying to implement it without using a VBA so that the fields are automatically updated.

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,595
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Expanding Array Formula to Include an OR option

    Hi

    Try:

    ...IF((day_1=$A$11)+(day_1=$A$12),ROW(day_1)),...
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,937
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Expanding Array Formula to Include an OR option

    To use an OR condition in an array formula you must add the two conditions - something like
    IF((day_1=$A$11)+(day_1=$A$12),....

    M.

  4. #4
    New Member
    Join Date
    Apr 2013
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expanding Array Formula to Include an OR option

    Great thanks @Marcelo Branco for that seems to work as expected

    Love this forum

    Quote Originally Posted by Marcelo Branco View Post
    To use an OR condition in an array formula you must add the two conditions - something like
    IF((day_1=$A$11)+(day_1=$A$12),....

    M.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,937
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Expanding Array Formula to Include an OR option

    You're welcome. I'm glad we helped.

    M.

  6. #6
    New Member
    Join Date
    Apr 2013
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expanding Array Formula to Include an OR option

    Is there a maximum number of + you can add or is it limited to 2?

    I've tried adding in another condition and getting a problem with formula dialog

    Quote Originally Posted by Marcelo Branco View Post
    To use an OR condition in an array formula you must add the two conditions - something like
    IF((day_1=$A$11)+(day_1=$A$12),....

    M.

  7. #7
    New Member
    Join Date
    Apr 2013
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expanding Array Formula to Include an OR option

    Disregard, I had a problem with some brackets but was convinced I had it correct first time!!!

    Quote Originally Posted by crookesa View Post
    Is there a maximum number of + you can add or is it limited to 2?

    I've tried adding in another condition and getting a problem with formula dialog

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •