vlookup issues

Ok, I'm going to try to explain this as best I can. I'd attach an example but the file is too large.

I have a workbook with four data tabs each with about 7000 lines and seven columns of info.

I have one results tab with specific listings which exist on one of the other four data tabs. I am using a vlookup formula to pull columns 3 and 4 from the appropriate data tab for the specific listings on the results tab.

The data which is being pulled is numeric, however formated as text due to some numbers having one or two zero's in the first two character fields.

The issue:

The vlookup formula will work for all of the cells with values begining with one or more zeros, however not all of the cells which start with a nine. It is only the 9.

First I figured it was a formating issue or formula issue. I've checked both - the data cell and formula cell appear identical. Additionally, not all of the cells with a nine in the first character field will not pull - some are pulling the correct data. I tried changing these to a numeric format - no go.

I cut and pasted values only into an a new spreadshet - same issue.

Finnally, I did check the character allotment with a =len formula. All of the fields are identical at 6 characters.

Anyone have any ideas. I, along with one other guy are the excel people here and we're both stumped. I showed it to one other person in the office - no go.

Help please. Thanks

Possibly not the cause but what formula are you using?

You may need to attach the worksheet, can you do that, or an edited version?

It's a simple vlookup formula to find an exact match. I've double checked the formula and it is working correctly at other parts of the workbook.

Even a partial file is over the limit for attachments.

Thanks anyway daddylonglegs

Originally Posted by pittguy

Ok, I'm going to try to explain this as best I can. I'd attach an example but the file is too large.

I have a workbook with four data tabs each with about 7000 lines and seven columns of info.

I have one results tab with specific listings which exist on one of the other four data tabs. I am using a vlookup formula to pull columns 3 and 4 from the appropriate data tab for the specific listings on the results tab.

The data which is being pulled is numeric, however formated as text due to some numbers having one or two zero's in the first two character fields.

The issue:

The vlookup formula will work for all of the cells with values begining with one or more zeros, however not all of the cells which start with a nine. It is only the 9.

First I figured it was a formating issue or formula issue. I've checked both - the data cell and formula cell appear identical. Additionally, not all of the cells with a nine in the first character field will not pull - some are pulling the correct data. I tried changing these to a numeric format - no go.

I cut and pasted values only into an a new spreadshet - same issue.

Finnally, I did check the character allotment with a =len formula. All of the fields are identical at 6 characters.

Anyone have any ideas. I, along with one other guy are the excel people here and we're both stumped. I showed it to one other person in the office - no go.

Help please. Thanks

I would attempt to put both data files in the exact format. You can do this by either using the =value formula or the =text formula (with the trim function).

Please contact me if you need further help.

you can email me the spreadsheet [email protected] if you want