In Excel, why does PERCENTAGE format display change when adding a PERIOD “.” on the IF/VLOOKUP formula?

Question

I'm using MS Excel 2016. I’m using this formula:

=IF(D8="","XX.XX%.",VLOOKUP(D8,P:Q,2,FALSE))

to display 12.34%, which is exactly how it appears on the source cell. However, as soon as I concatenate a PERIOD – since it's the end of the sentence -- on the formula:

=IF(D8="","XX.XX%.",VLOOKUP(D8,P:Q,2,FALSE))&"."

I would get 0.1234. on display.

Can anyone help me with how can I get 12.34%. as the result?

Answer 1

Since you are concatenating a number and a string, Excel automatically changes the number format to the General format. To define the format you want for your number, use the TEXT function.

TEXT(VLOOKUP(D8,P:Q,2,FALSE),"0.0%")&"."