VLOOKUP from data table not producing correct format

[Kreese1003]

Hello!

I currently have a worksheet where I have created a vlookup to correspond with a drop down menu to auto populate results based on a given table. In the original table the formats have dollar signs, percentages, dates, etc. all formatted correctly.

In the corresponding sheet that auto populates given a certain selection chosen from a drop down menu, nothing is formatted the way I want. I was able to get the date to change by adding TEXT to the beginning of the formula and “MM/DD/YYY” to the end but cannot get the other cells to display commas in a number, dollar signs, or percentages.

An example of formula not having the correct format is as follows:

=“”&IFERROR(vlookup(vlookup(rnginvoice,invoicesmain,1,false),invoicesmain,8,false),””””)

I have already tried to manually change the formatting of the cell and no selections change it.

Thanks for your help!

Kayla

 

[mole999]

formats don't usually follow the data

 

[Kreese1003]

formats don't usually follow the data

What do you mean by this? Are you saying there is not a way to change this and it will always appear as text even if I change the formatting? Why was I able to change the date format, but not currency or percentages?

Thanks for your help.

 

[mole999]

a number is a number, text is text. as you said you changed the format using TEXT

 

[Twollaston]

Hello!

I currently have a worksheet where I have created a vlookup to correspond with a drop down menu to auto populate results based on a given table. In the original table the formats have dollar signs, percentages, dates, etc. all formatted correctly.

In the corresponding sheet that auto populates given a certain selection chosen from a drop down menu, nothing is formatted the way I want. I was able to get the date to change by adding TEXT to the beginning of the formula and “MM/DD/YYY” to the end but cannot get the other cells to display commas in a number, dollar signs, or percentages.

An example of formula not having the correct format is as follows:

=“”&IFERROR(vlookup(vlookup(rnginvoice,invoicesmain,1,false),invoicesmain,8,false),””””)

I have already tried to manually change the formatting of the cell and no selections change it.

Thanks for your help!

Kayla

you can try these

=TEXT(A1,"0%")
=TEXT(B1,"$#,##0.00")

 

[Kreese1003]

you can try these

=TEXT(A1,"0%")
=TEXT(B1,"$#,##0.00")

Yes! That worked!! Thank you so much for your help.

 

[jtakw]

In the original table the formats have dollar signs, percentages, dates, etc. all formatted correctly.

In the corresponding sheet that auto populates given a certain selection chosen from a drop down menu, nothing is formatted the way I want. I was able to get the date to change by adding TEXT to the beginning of the formula and “MM/DD/YYY” to the end but cannot get the other cells to display commas in a number, dollar signs, or percentages.

An example of formula not having the correct format is as follows:

=“”&IFERROR(vlookup(vlookup(rnginvoice,invoicesmain,1,false),invoicesmain,8,false),””””)

I have already tried to manually change the formatting of the cell and no selections change it.

Kayla

Hi,

The results of your VLOOKUP got turned into TEXT because you added a Blank (above in red); therefore, Cell Formatting will Not work, if you Remove the red part from the formula, then you can format the formula/result cell to display as you wish.