Why doesn't my Vlookup work anymore?

[kajero]

I have three tabs. The first tab contains the table with VLOOKUP formulas. The second tab is the budget I download from the network server. The third tab contains information I download from PeopleSoft. The file downloads as a .cvs file and I save the downloaded file as an Excel file and then copy and paste the data into my worbook,


The lookup number will only return information from one of the tabs, but not both. (I have them formulas in separate columns) I know it has something to do with the way thet project numbers are formatted. If I copy and paste a project number from the budget tab to the VLookup tab, I get the information from the budget tab. If I copy and paste a project number the PeopleSoft tab into the VLookup tab, I get the PeopleSoft information.


What I don’t understand is my formulas worked great for the last couple of months, but something must have changed because now they just don’t work.


I have tried formatting the numbers anyway I can, but nothing works. Could someone advise me?

Thanks in advance for any help you can give me.

 

[kajero]

Try formatting the numbers so they all show two decimal places.

.4 should show as .40

That's the only thing I can suggest.

I am not very good at putting other people's spreadsheets together for analyzation so I am only suggesting.

 

[pawest]

That was my first thought, too, but that doesn't work.

Here's a hackish solution (although I hate this solution):

Convert everything to text like so:

Cell B3: =TEXT(B2,"#,##0.00") <-- this calls B2 which is normal input such as 0.17 or 0.4

Vlookup function: =VLOOKUP(TEXT(B3,"#,##0.00"),D:H,2,0) <-- using the TEXT function again just to be safe

Range in column D: =TEXT((D16+0.01),"#,##0.00")


Again, this works but I am not a fan of this solution.

Does anyone know any other solution using a standard vlookup function?

Thanks! Pete

 

[kajero]

I did get it to work with Concatenate, but you can't sum columns because everything is text.