Thread: Expanding Array Formula to Include an OR option Thanks:  1 Post #5251456 (1) Likes:  1 Post #5251456 (1)

1. 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.  Reply With Quote

2. Re: Expanding Array Formula to Include an OR option

Hi

Try:

...IF((day_1=\$A\$11)+(day_1=\$A\$12),ROW(day_1)),...  Reply With Quote

3. 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.  Reply With Quote

4. Re: Expanding Array Formula to Include an OR option

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

Love this forum  Originally Posted by Marcelo Branco 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.  Reply With Quote

5. Re: Expanding Array Formula to Include an OR option

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

M.  Reply With Quote

6. 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 Originally Posted by Marcelo Branco 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.  Reply With Quote

7. 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!!! Originally Posted by crookesa 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  Reply With Quote

User Tag List

cell, criteria, fields, formula, values 