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

 

[Peter_SSs]

Here is one way.

23 02 28.xlsm
	A	B	C	D	E
1	Task 1	Ranking		Task 1
2	Audit	0		Audit	0
3	Complaince	2		Complaince	2
4	Testing	4		Testing	4
5	Monitoring			Monitoring
6	Attendence	3		Attendence	3
7	Systen Check	5		Systen Check	5
8	Network	1		Network	1
9	Technology			Technology
10	Desks	0		Desks	0
11	Lighting			Lighting
Sheet2 (2)

Cell Formulas
Range	Formula
E2:E11	E2	=IF(VLOOKUP(D2,A$2:B$11,2,0)="","",VLOOKUP(D2,A$2:B$11,2,0))

 

[awoohaw]

how about:

Book1
	A	B	C	D
1	Task 1	Ranking
2	Audit	0.0		0
3	Complaince	2.0		2
4	Testing	4.0		4
5	Monitoring
6	Attendence	3.0		3
7	Systen Check	5.0		5
8	Network	1.0		1
9	Technology
10	Desks	0.0		0
11	Lighting
12
Sheet5

Cell Formulas
Range	Formula
D2:D12	D2	=IF(ISNUMBER(VLOOKUP(A2,$A$2:$B$11,2,0))=FALSE,"",VLOOKUP(A2,$A$2:$B$11,2,0))

 

[Arts]

Hi Peter

Thanks for the quick response, at current I am using IFERROR with INDEX, MATCH Below). Could the IFERROR formula be edited to use the below or would I need incorporate your formula with the IF Function?

=IFERROR(INDEX('Task'!$D$10:$CZ$2296,MATCH(Task'!$C10,'Task'!$C$10:$C$2296,FALSE),MATCH('Task'!D$9,'task'!$D$9:$CZ$9,FALSE)),"")

 

[Peter_SSs]

=IFERROR(INDEX('Task'!$D$10:$CZ$2296,MATCH(Task'!$C10,'Task'!$C$10:$C$2296,FALSE),MATCH('Task'!D$9,'task'!$D$9:$CZ$9,FALSE)),"")

What is the name of the worksheet that this formula is on?

 

[Arts]

What is the name of the worksheet that this formula is on?

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

(for some reason the formula I provided above didn't copy over properly)

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

 

[Arts]

how about:

Book1
	A	B	C	D
1	Task 1	Ranking
2	Audit	0.0		0
3	Complaince	2.0		2
4	Testing	4.0		4
5	Monitoring
6	Attendence	3.0		3
7	Systen Check	5.0		5
8	Network	1.0		1
9	Technology
10	Desks	0.0		0
11	Lighting
12
Sheet5

Cell Formulas
Range	Formula
D2:D12	D2	=IF(ISNUMBER(VLOOKUP(A2,$A$2:$B$11,2,0))=FALSE,"",VLOOKUP(A2,$A$2:$B$11,2,0))

Hi

Apologies I wasn't ignoring your message I think it came through as I was responding to Peter so thank you for replying.

I did try your method but this still seems to return me back a blank and not a 0.

Saying that I did edit my formula to incorporate Peter's formula (IF) as well and this still returned me a blank instead of a 0

 

[awoohaw]

Hi

Apologies I wasn't ignoring your message I think it came through as I was responding to Peter so thank you for replying.

I did try your method but this still seems to return me back a blank and not a 0.

Saying that I did edit my formula to incorporate Peter's formula (IF) as well and this still returned me a blank instead of a 0

not sure why that is happening. As I have blanks and zeros in min workbook I posted. But, it looks like you have a solution. Which is the ultimate desire. Best wishes.

 

[Arts]

not sure why that is happening. As I have blanks and zeros in min workbook I posted. But, it looks like you have a solution. Which is the ultimate desire. Best wishes.

Sorry for the delay I had a deadline to meet and ended up doing it manually in the end

It's strange it seems to work on another work book (working examples below) but within my initial sheet still seems to fall over. I even changed the number within a blank to check if the formula was working which it was but when changing it back to 0 no avail....Guess one of those excel things.

Thanks for your input on this none the less.

 

[awoohaw]

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