Show zero from a formula result but not show zero where there are blanks

[Arts]

Hi all

I'm sure there is a simple fix for this but can't seem to find it.

I am doing a look up which works fine and this is pulling back ranking numbers.

The issue I am having that some tasks are ranked 0. (this is so they have been acknowledged and soon to be ranked so a 0 has been marked next to it)

The issue I am having is that it doesn't seem to pull back the "0" where there is a "0" value and if I turn on show zero values through the advanced setting (screen shot below) this brings back zero values for all the blanks.

Is there anyway to just show the 0 from the values below when doing the look up but not show zero for the blanks. (the below is dummy data I have a dataset with over 2,000 rows where I need to apply this)

Task 1	Ranking
Audit	0​
Complaince	2​
Testing	4​
Monitoring
Attendence	3​
Systen Check	5​
Network	1​
Technology
Desks	0​
Lighting

Thanks

 

[Arts]

can you post a mini workbook using the xl2bb add in?

I'm currently at work and won't be able to install the xl2bb player unfortunately. I'll keep plugging away as I want to try and avoid doing this manually again soon.

I've checked the basics ie cell formatting and conditional formats, all seem fine there too...

 

[Peter_SSs]

"Task" - is the name of the spreadsheet the formula is on

Thanks. You should never use that sheet name in a formula that is on that sheet. It can lead to errors in some cases (not the problem here though)
=IFERROR(INDEX('Tasks List'!$D$10:$CZ$2296,MATCH('Task'!$C10,'Tasks List'!$C$10:$C$2296,FALSE),MATCH('Task'!D$9,'Tasks List'!$D$9:$CZ$9,FALSE)),"")

See if this is what you want.

Arts.xlsm
	C	D	E	F
9	Task 1	Ranking	Amount	Size
10	Audit	0		3
11	Complaince	2	3	0
12	Testing	4	0	0
13	Monitoring		0
14	Attendence	3	0
15	Systen Check	5	5
16	Network	1		2
17	Technology		6	0
18	Desks	0		0
19	Lighting		3	0
Tasks List

Arts.xlsm
	C	D	E	F
9		Amount	Size	Ranking
10	Audit		3	0
11	Complaince	3	0	2
12	Testing	0	0	4
13	Monitoring	0
14	Attendence	0		3
15	Systen Check	5		5
16	Network		2	1
17	Technology	6	0
18	Desks		0	0
19	Lighting	3	0
Task

Cell Formulas
Range	Formula
D10:F19	D10	=IFERROR(IF(INDEX('Tasks List'!$D$10:$CZ$2296,MATCH($C10,'Tasks List'!$C$10:$C$2296,FALSE),MATCH(D$9,'Tasks List'!$D$9:$CZ$9,FALSE))="","",INDEX('Tasks List'!$D$10:$CZ$2296,MATCH($C10,'Tasks List'!$C$10:$C$2296,FALSE),MATCH(D$9,'Tasks List'!$D$9:$CZ$9,FALSE))),"")

 

[Arts]

Thanks. You should never use that sheet name in a formula that is on that sheet. It can lead to errors in some cases (not the problem here though)
=IFERROR(INDEX('Tasks List'!$D$10:$CZ$2296,MATCH('Task'!$C10,'Tasks List'!$C$10:$C$2296,FALSE),MATCH('Task'!D$9,'Tasks List'!$D$9:$CZ$9,FALSE)),"")

See if this is what you want.

Arts.xlsm
	C	D	E	F
9	Task 1	Ranking	Amount	Size
10	Audit	0		3
11	Complaince	2	3	0
12	Testing	4	0	0
13	Monitoring		0
14	Attendence	3	0
15	Systen Check	5	5
16	Network	1		2
17	Technology		6	0
18	Desks	0		0
19	Lighting		3	0
Tasks List

Arts.xlsm
	C	D	E	F
9		Amount	Size	Ranking
10	Audit		3	0
11	Complaince	3	0	2
12	Testing	0	0	4
13	Monitoring	0
14	Attendence	0		3
15	Systen Check	5		5
16	Network		2	1
17	Technology	6	0
18	Desks		0	0
19	Lighting	3	0
Task

Cell Formulas
Range	Formula
D10:F19	D10	=IFERROR(IF(INDEX('Tasks List'!$D$10:$CZ$2296,MATCH($C10,'Tasks List'!$C$10:$C$2296,FALSE),MATCH(D$9,'Tasks List'!$D$9:$CZ$9,FALSE))="","",INDEX('Tasks List'!$D$10:$CZ$2296,MATCH($C10,'Tasks List'!$C$10:$C$2296,FALSE),MATCH(D$9,'Tasks List'!$D$9:$CZ$9,FALSE))),"")

Thanks for this Peter will give this a whirl, I'm not due at work until the afternoon today but wanted to acknowledge your reply.