IF Formula and blank answer

[Guest]

Hello this is my formula
=IF(ISNA(VLOOKUP($A4,'PG Disc'!$E$2:$U$632,17,FALSE))," ",VLOOKUP($A4,'PG
Disc'!$E$2:$U$632,17,FALSE)) Here is the thing the column where the answer
goes is formatted as percentage. if column A is blank is till gives me
0.00%. what I want is if column A is blank the answer to be blank (empty
cell) thanks

 

[JE McGimpsey]

One way:

=IF(ISNA(MATCH($A4,'PG Disc'!$E$2:$E$632,FALSE)),"",IF(VLOOKUP($A4,
'PG Disc'!$E$2:$E$632,17,FALSE)="", "", VLOOKUP($A4, 'PG
Disc'!$E$2:$E$632,17,FALSE)))

 

[Dave Peterson]

=if(isna(vlookup(...)),"",if(vlookup(...)="","",vlookup(...)))


Check for an error. Check for empty. Then return the real value.

or

=if(iserror(1/len(vlookup(...)),"",vlookup(...))

 

[Guest]

The formula seems to work for me. Did you check your look up data. There
may be true 0% values that you are finding rather that #N/A. Sorry if that
doesn't help.

 

[Guest]

Thanks JE McGimpsey
I copied the formula but it does not work. In this case the answer should be
50.00% but it is blank again thanks

 

[JE McGimpsey]

Copied the range incorrectly. Replace

$E$2:$E$632

with

$E$2:$U$632

in the VLOOKUP()s (not the MATCH())

 

[Peo Sjoblom]

It's because there is no match, compare A4 to the match range, if it is a
number it might be decimals difference and if it is text then there might be
extra space or hidden html characters

 

[Guest]

Thanks Dave for Saving the day!

=if(isna(vlookup(...)),"",if(vlookup(...)="","",vlookup(...)))


Check for an error. Check for empty. Then return the real value.

or

=if(iserror(1/len(vlookup(...)),"",vlookup(...))