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),"")))

 

[Fluff]

Please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[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),"")))

in the picture below bones is = 1,0,0,0,0 it should return the department name

 

[Fluff]

Can you please post some sample data (not an image) as requested.

 

[MAlhash]

Can you please post some sample data (not an image) as requested.

I have tried to post, but I failed. is there another way to send my sheet.
thank you.

 

[Fluff]

In what way did it fail?

 

[MAlhash]

In what way did it fail?

I could not download the add in

 

[Fluff]

In that case can you simply copy/paste some data from Excel to the board.

 

[MAlhash]

In that case can you simply copy/paste some data from Excel to the board.

Table Slicers on Excel Dashboards

BONE & JOINT CENTER	1	0	0	0	0
HBKMC BUILDING 329	0	0	0	0	0
INTERNAL MEDICINE CLINIC	0	0	0	0	0
OPD LEVEL 1	2	2	0	0	0
OPD LEVEL 2	9	1	0	0	0
OPD LEVEL 3	0	0	1	0	0
OPD LEVEL 4	1	0	0	0	0
OUTPATIENT DEPARTMENT	1	0	1	0	0
PEDIATRIC	0	0	0	0	0
PULMONARY	0	2	0	0	0
QATAR METABOLIC INSTITUTE	1	0	0	0	0
STAFF MEDICAL CENTER	0	0	0	1	0
SURGICAL SPECIALTY CENTER - OUTPATIENT DEPARTMENT	5	3	1	0	0

when i have used this formula, it has returned me this table. pulmonary for example is missing. because first value is zero , but it have number 2 in the second value so pulmonary should be shown

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

Table Slicers on Excel Dashboards

BONE & JOINT CENTER

OPD LEVEL 1

OPD LEVEL 2

OPD LEVEL 4

OUTPATIENT DEPARTMENT

QATAR METABOLIC INSTITUTE

SURGICAL SPECIALTY CENTER - OUTPATIENT DEPARTMENT

 

[Fluff]

Thanks for that.
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:I11	I4	=FILTER(B4:B16,(C4:C16>0)+(D4:D16>0))
Dynamic array formulas.