Expanding Array Formula to Include an OR option

[crookesa]

hi,

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

=IF(ISERROR(INDEX(employee_names,SMALL(IF([B]day_1=$A$11[/B],ROW(day_1)),ROW(1:1))-1,1))," ",INDEX(employee_names,SMALL(IF([B]day_1=$A$11[/B],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.

 

[pgc01]

Hi

Try:

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

 

[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.

 

[crookesa]

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

Love this forum

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.

 

[Marcelo Branco]

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

M.

 

[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

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.

 

[crookesa]

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

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