Expanding Array Formula to Include an OR option

crookesa

Board Regular
Joined
Apr 11, 2013
Messages
62
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([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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

Board Regular
Joined
Apr 11, 2013
Messages
62
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

Board Regular
Joined
Apr 11, 2013
Messages
62
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,306
Messages
5,449,572
Members
405,572
Latest member
Oberon

This Week's Hot Topics

Top