index, small, rows .... problem

[MAlhash]

Hello,

I have Similar problem. the first if formula before the OR is working greatly, however after or its not giving me the data which i need.
basically, I have department names in a column, each department have two values separately. for example, if the first value is zero and the second values is 1 for the debarment name, it should return department name. but if both values equal 0 it should not return the department name. so, the condition is if one of the values is =0 return department name, if both 0 do not return, if both numbers return.

for me, the formula is working like the following.
if value 1 contain number and value2 contain zero it will return department name, which is true. however, if value 1 is =0 and value 2 = number it will not return the depatment name, which is wrong. it should return.

=IFERROR(INDEX($C$17:$C$29,SMALL(IF($D$17:$D$29>0,ROW($D$17:$D$29)),ROWS(K$14:K20))-ROW($D$17:$D$17)+1), OR(IFERROR(INDEX($C$17:$C$29,SMALL(IF($E$17:$E$29>0,ROW($E$17:$E$29)),ROWS(K$14:K20))-ROW($E$17:$E$17)+1),"")))

 

[MAlhash]

Thank you for the formula, but unfortunately the Filter function is not available or activated for me. is there any other formula that can be used.
thank you

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4		BONE & JOINT CENTER	1	0	0	0	0		BONE & JOINT CENTER
5		HBKMC BUILDING 329	0	0	0	0	0		OPD LEVEL 1
6		INTERNAL MEDICINE CLINIC	0	0	0	0	0		OPD LEVEL 2
7		OPD LEVEL 1	2	2	0	0	0		OPD LEVEL 4
8		OPD LEVEL 2	9	1	0	0	0		OUTPATIENT DEPARTMENT
9		OPD LEVEL 3	0	0	1	0	0		PULMONARY
10		OPD LEVEL 4	1	0	0	0	0		QATAR METABOLIC INSTITUTE
11		OUTPATIENT DEPARTMENT	1	0	1	0	0		SURGICAL SPECIALTY CENTER - OUTPATIENT DEPARTMENT
12		PEDIATRIC	0	0	0	0	0
13		PULMONARY	0	2	0	0	0
14		QATAR METABOLIC INSTITUTE	1	0	0	0	0
15		STAFF MEDICAL CENTER	0	0	0	1	0
16		SURGICAL SPECIALTY CENTER - OUTPATIENT DEPARTMENT	5	3	1	0	0
17
Master

Cell Formulas
Range	Formula
I4:I13	I4	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$4:$B$16)/SIGN(($C$4:$C$16>0)+($D$4:$D$16>0)),ROWS(I$4:I4))),"")

 

[MAlhash]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4		BONE & JOINT CENTER	1	0	0	0	0		BONE & JOINT CENTER
5		HBKMC BUILDING 329	0	0	0	0	0		OPD LEVEL 1
6		INTERNAL MEDICINE CLINIC	0	0	0	0	0		OPD LEVEL 2
7		OPD LEVEL 1	2	2	0	0	0		OPD LEVEL 4
8		OPD LEVEL 2	9	1	0	0	0		OUTPATIENT DEPARTMENT
9		OPD LEVEL 3	0	0	1	0	0		PULMONARY
10		OPD LEVEL 4	1	0	0	0	0		QATAR METABOLIC INSTITUTE
11		OUTPATIENT DEPARTMENT	1	0	1	0	0		SURGICAL SPECIALTY CENTER - OUTPATIENT DEPARTMENT
12		PEDIATRIC	0	0	0	0	0
13		PULMONARY	0	2	0	0	0
14		QATAR METABOLIC INSTITUTE	1	0	0	0	0
15		STAFF MEDICAL CENTER	0	0	0	1	0
16		SURGICAL SPECIALTY CENTER - OUTPATIENT DEPARTMENT	5	3	1	0	0
17
Master

Cell Formulas
Range	Formula
I4:I13	I4	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$4:$B$16)/SIGN(($C$4:$C$16>0)+($D$4:$D$16>0)),ROWS(I$4:I4))),"")

Many Thank MAM it worked.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[MAlhash]

You're welcome & thanks for the feedback.

You are more than Welcome Mam.

I have another question if it's possible!

I do not know why when I have sent the Excel sheet which I am working on from my PC to my laptop it shows me values error for all the data. Can this problem be fixed.

Thank you.

 

[Fluff]

What do you mean by

it shows me values error

 

[MAlhash]

What do you mean by

#VALUE! its appearing on the whole cells and sheets.​

 

[Fluff]

You should not get that error with the formula I posted, as it has IFERROR to mask all errors.

 

[MAlhash]

You should not get that error with the formula I posted, as it has IFERROR to mask all erro

Sorry for the long time. I had issue with my internet.
No Mam, it is not because of your formula. I have done one table and after that all my sheet become value# error.

I will attach before and after.

 

[Fluff]

As that is a totally different formula & nothing to do with this thread, you need to start a new thread. Thanks.

Also I am a bloke, not woman.