CODEWITHSUNDEEP
exceldecimalvlookup
AFab
AFab

Reputation: 25

How can I display the decimals of a number in VLOOKUP formula?

I have this formula in my Sheet 1 returning a value from Data sheet

="** "&(VLOOKUP(D9,Data!A2:B121,2,FALSE)&" **")

In the Data sheet I have the number 6,524.60

But in my current sheet when VLOOKUP the number displays like 

** 6805.3 **

I need to display it as

** 6,805.30 **

Is there any way of doing that? My macro sheet already has the SpellNumber VBA on it...

Upvotes: 2

Views: 2440

Answers (2)

logicOnAbstractions
logicOnAbstractions

Reputation: 2580

What you are looking for TEXT() and I would recommand to use CONCATENATE:

=CONCATENATE("** ",TEXT(VLOOKUP(D9,Data!A2:B121,2,FALSE),"$#,##0.00")," **")

Upvotes: 0

ali srn
ali srn

Reputation: 573

You can use Text function for that as below in your function.

="** "&(TEXT(VLOOKUP(A1;A1:B1;2;FALSE);"#,#.00")&" **")

Upvotes: 2

Related Questions