Array formula to bring back results if column DI20 onwards has a value of 1 in a cell
Hi Experts using Excel 2010
I have the following formula which works just fine….
Array Formula
=INDEX('Data Entry'!BF$20:BF$532,SMALL(
However, i want to add an additional step into the above formula so the formula also looks in column DI20 onwards and if the cell has a value of 1 return back the result.
I have the following formula which works just fine….
Array Formula
=INDEX('Data Entry'!BF$20:BF$532,SMALL(
However, i want to add an additional step into the above formula so the formula also looks in column DI20 onwards and if the cell has a value of 1 return back the result.
ASKER
Hi Rgonzo
The formula returns #num! When I drag it down the first cell returns the correct value then errors
The formula returns #num! When I drag it down the first cell returns the correct value then errors
Could you send a sample
ASKER
Tricky...to...do so..
Entered as array?
Maybe
=INDEX('Data Entry'!BF$20:BF$532,SMALL(IF('Data Entry'!$DI$20:$DI$532=1,IF('Data Entry'!$BF$20:$BF$532>=$C$1),IF('Data Entry'!$BF$20:$BF$532<=$F$1,ROW('Data Entry'!$BF$20:$BF$532)-ROW('Data Entry'!$BF$20)+1))),ROWS(A$6:A6)))ASKER
Yes..as an array...and apologise that I cannot upload a file
Formula posted by Rgonzo HERE should work for you.
ASKER
Ok rgonzo I have attached a file...with test data...not sure what u may have done wrong.
test-data_.xlsx
test-data_.xlsx
You need to wrap the formula within IFERROR like this....
The column DI on Data Entry Sheet doesn't meet the criteria i.e it is blank and it doesn't contain 1 in any of its cell.
=IFERROR(INDEX('Data Entry'!A$2:A$6,SMALL(IF('Data Entry'!$DI$2:$DI$6=1,IF('Data Entry'!$BF$2:$BF$6>=$C$1,IF('Data Entry'!$BF$2:$BF$6<=$F$1,ROW('Data Entry'!$BF$2:$BF$6)-ROW('Data Entry'!$BF$2)+1))),ROWS(A$3:A3))),"")The column DI on Data Entry Sheet doesn't meet the criteria i.e it is blank and it doesn't contain 1 in any of its cell.
ASKER
Not sure what I may have done worng..typo
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks and much appreciated experts for the excellent feedback.
Posting one more question in relation to above..
Posting one more question in relation to above..
pls try
Open in new window
Regards