Formatting value to currency - IF statement and Pivot tables not working........

[LeeMan88]

Hi</SPAN></SPAN>

I have a somewhat strange problem</SPAN></SPAN>

I have an IF statement - =IFERROR(TRIM(VLOOKUP($J6,Personnel,5,FALSE)),""), that returns the correct value BUT the issue is the format of the returned data.</SPAN></SPAN>

The source table contains $105.79 in currency format. </SPAN></SPAN>

The value being returned is 105.79 ie NOT $ currency format.
I have tried formatting the cell directly but this does not work.
I have also entered the value in the source table directly as $105.79 and shows as “currency” format but still returns 105.79.</SPAN></SPAN>

When I then create a pivot table, it shows the value as 105.79 and cannot be formatted to a currency value.</SPAN></SPAN>

Any ideas would be helpful.</SPAN></SPAN>

Thanks</SPAN></SPAN>

 

[CROY1985]

Trim seems to format as text and overrides anything else:

can you just use:

=IFERROR(VLOOKUP(J6,A1:E5,5,FALSE),"")

 

[CROY1985]

if you need to trim it, you can use:

=IFERROR(VALUE(TRIM(VLOOKUP(J6,Personnel,5,FALSE))),"")

Sorry, i didn't use your exact references in my first response.

P.s if you are using this formula for a a lot of data, you may have a better time with an index/match formula as they run more efficiently/quicker.

=IFERROR(VALUE(TRIM(Index(values,MATCH(J6,Personnel)))),"")

where values is the range containing the $ values.

 

[LeeMan88]

Thanks. Made the changes for TRIM and it seems to be OK..... Had me stumped. Answer was so simple - once someone who knows how to looked at it !.

Cheers

 

[samyscraps01]

The formula above works great. However, How can I keep the formatting to turn the 1.00 into $1.00 but also retain words like Flat Rate in the cells that have words? When I entered the formula it just deleted the words. Thank you.