Formatting Number Resulting from VLOOKUP

[tgallant]

Hey Folks, I couldn't find anything on the forum about this so I'm hoping someone can help.

I have a data extra that I take out of a reporting tool and I use a VLOOKUP on another tab to get a value out of that data. The problem is, the data in the extract comes in showing 6 spaces past the decimal, but depending on the field, I either want it to show no positions past the decimal or a maximum of 2.

My VLOOKUP looks in the data for 2 criteria and brings back a numeric value. The formula I'm using is:

=INDEX('Data.xls'!ALLDATA,MATCH(1,('Data.xls'!AGENTNAME=A6)*('Data.xls'!KPI="HOLD_TIME"),0),3)

As an example, this brings back data of 144.676020

I need that field to display 144.68.

In my mind, it sounded simple. Right click, Format Cells, Number, Decimal places: 2. BUT, that does absolutely nothing when I do it. Even the Sample it shows on that page still shows the 6 numbers after the decimal. Any field not using a VLOOKUP allows formatting without issue so I'm guessing this is in some way due to the VLOOKUP bringing in the data.

Any thoughts on how to modify my VLOOKUP formula to format the data into ###.## or ###? Is this something that can be done through the formula or do I have to go into VBA (which I'm far from strong in)?

Thanks in advance.

 

[Aladin Akyurek]

Try:

=ROUND(INDEX('Data.xls'!ALLDATA,MATCH(1,('Data.xls'!AGENTNAME=A6)*('Data.xls'!KPI="HOLD_TIME"),0),3),2)

 

[tgallant]

Try:

=ROUND(INDEX('Data.xls'!ALLDATA,MATCH(1,('Data.xls'!AGENTNAME=A6)*('Data.xls'!KPI="HOLD_TIME"),0),3),2)

Wow, was it really that easy???? I tried it and it worked perfectly for me. For some reason it also solved another problem I was having with conditional formatting. Thank you a ton! I researched the hell out of this online and everything was telling me VBA. Thank you!

 

[Aladin Akyurek]

You are welcome.