Why doesn't my Vlookup work anymore?


Board Regular
Jun 28, 2009
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.

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
One of the problems I faced with external data sources is change in field format - from value to text format or vice versa.
Upvote 0
I made sure all the field formats were identical. I am not sure how I could present data so it wouldn't be confusing.

I finally remembered an old trick a co-worker taught me 8 years ago. I inserted a column and then multiplied all the project numbers (from PeopleSoft) by 1. Then I pasted the calculated column back into column A as values and deleted the inserted column. The vlookup table finds everything now. PeopleSoft . . I will never understand it.
Upvote 0
Glad you got resolution.

FYI, a quicker/simpler method to do the same thing without inserting a column, and creating a new data set and copy/pasting that to the original..

copy any BLANK cell.
Highlight the problematic column of numbers
Right Click - Paste Special - Values - ADD - Ok

Hope that helps.
Upvote 0
Thank you so much for the added suggestion. If it weren't so late, I would boot up the work computer and implement what you suggested. Thanks again, I will let you know how it works out.
Upvote 0
I thought I had the problem solved. Actually I did get it work when I paste my data from Excel spreadsheets where I have keyed in data to transfer.

Now I have pasted new data from a PeopleSoft query. When I paste numbers in the vlookup does not work. All data on all tabs is text. When I try multiply the new numbers by 1; it doesn't calculate but the formula prints out 1*A3. I recaculated but nothing happens. I imagine it something in the way PeopleSoft provides the numbers. The query comes in .csv file. I also have tried to saving it as an Excel file, but that doesn't work either.

Could someone offer me some advice? I have spent hours trying to figure this out.

How could I paste in a message what is happening? I have to keep a tracker for the boss and if I could do it this way, I would only have to do 1 tracker instead of 2.
Upvote 0
I FIGURED IT OUT!! If I save the file as a text file, close it, and open it in Excel as the text file and do text delimited, when I paste the the numbers in the tracker with the formulas EVERYTHING works. It only took me 4 hours to figure it out!
Upvote 0
I'm following up on this post as I have a similar vlookup problem. Rather than resulting in using a paste special, save as CSV, or writing a macro, I want to be able to use a function like vlookup or index/match.

Here's what's going on:

I have data in D15:H601.

The vlookup looks at all numbers in column D.

The formulas in column D are like so: =D16+0.01 and they range from 0.01 to 0.46.

The vlookup statement is =VLOOKUP(B3,D:H,2,0) where B3 = 0.4

The odd thing is the vlookup works on non-tenth numbers such as 0.12 or 0.39, but it does not work on 0.1 or 0.4.

Any thoughts on how I can solve this with vlookup and without using some sort of text function? Thanks!
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back