vlook up cell to show blank

[PEAKCAB]

Hello,

I am trying to make my invoice process easier and from my customer base populate the address cells on the invoice using vlookup which i now have sorted; however on the cells in the lookup that are blank, on the invoice it shows a 0 which i would like to show as a blank.

i am using the following formula

=IFERROR(VLOOKUP($A$22,Account!1:1048576,7,FALSE),"")

Can anybody point me in the right direction?

Regards,

 

[Yongle]

one way

=IF(IFERROR(VLOOKUP($A$22,Account!1:1048576,7,FALSE),"")=0,"",IFERROR(VLOOKUP($A$22,Account!1:1048576,7,FALSE),""))

 

[PEAKCAB]

Brilliant! Thanks

 

[Yongle]

glad it helped
thanks for the feedback

 

[Aladin Akyurek]

Shorter...

=IFERROR(T(VLOOKUP($A$22,Account!1:1048576,7,0)),"")​

 

[Yongle]

@Aladin Akyurek your formula does not work for me

 

[Aladin Akyurek]

@Aladin Akyurek your formula does not work for me

Try again... If a VLOOKUP formula is expected to return text, T(VLOOKUP(…)) will return non-zero length text as is and a zero-length test as blank.

 

[Yongle]

@Aladin Akyurek
Thanks for the explanation
- your formula works with text but returns blank for numeric fields

An address field like postcode 73301 may contain only numerics
- is it possible to amend your formula to handle both numerics and text?

 

[Aladin Akyurek]

It's meant to work for a VLOOKUP formula, assumed to return text or text numbers...

If the VLOOKUP formula is expected to return a numeric result, one can force it with...

=IFERROR(T(VLOOKUP($A$22,Account!1:1048576,7,0)&""),"")

the result ow which might require an additional step for further processing.

 

[Yongle]

@Aladin Akyurek That is a neat trick - thank you