vlookup and return exact number of decimal places indicated in a percentage

[AnnaBanana11]

Hi...I've searched the internet far and wide and am unable to find a solution. I have a vlookup that returns a percentage. My issue is that the number of decimal places in the percentages vary from 0-3 and I want it returned with the ONLY the exact number of decimal places without having to change it. For example, I want 6.3% automatically displayed as 6.3%, 6.75% as 6.75%, 6.875% as 6.875%, and so on. but instead it returns my result as the cell is formatted, i.e. if I have it set to four decimal places, it returns 6.3000%, 6.7500% and 6.8750%. Is there a way to achieve what I am looking for. My vlookup is as follows:

=IFERROR(VLOOKUP(RIGHT(B8,5)+0,'[CustomerBillingInfo.xlsx]AZ Sales Tax'!$A:$B,2,FALSE),"0.00%")

 

[jasonb75]

Try a custom format of 0.0#%

You may need to use conditional formatting to show 0 decimal places correctly with a rule of

=MOD(cell,1)=0

and a format of 0%

I believe that this should work but I don't have laptop to hand in order to test it.

 

[AnnaBanana11]

That's it!! Who knew it could be so simple! Just needed the custom format of 0.0#%. No need for the conditional formatting. Thank you!!