Very strange: vlookup shows only half of results characters!?

ulsterijussi

New Member
Joined
Apr 6, 2011
Messages
9
Here's what is bugging me;

have 2 workbooks:
1st is a production lot sheet
2nd is a weekly production planning sheet

I want to type in a lot# on 1st workbook and then get all the relevant data from 2nd workbook.

2nd workbook (weekly production planning) has 52 sheets (hence 52 weeks in a year =)), and the production lot #'s are in column A in descending order. The data (numbers and text, such as raw material type, thickness etc.) are in columns B to K.

So far I have installed morefunc to be able to access the second workbook by THREED func.
I´ve tried VLOOKUP and INDEX/MATCH functions. With VLOOKUP I could get the numbers returned correctly, but not text.. If a cell had the text "MONKEY" the function would return only half of it, ie MON. No matter what I wrote in the cell, Excel would only return half of the letters!


=VLOOKUP(B2;THREED('[PRODUCTIONPLANNING.xlsx]1:52'!A1:K44);2;0)</pre>Grateful for any help I can get!

Ulsteri
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
What does this...

=LEN(VLOOKUP(B2;THREED('[PRODUCTIONPLANNING.xlsx]1:52'!A1:K44);2;0))

return?
 

ulsterijussi

New Member
Joined
Apr 6, 2011
Messages
9
Hi Aladin,

In case cell to look up contains word MONKEY :
LEN + VLOOKUP returns = 6 (correct)
VLOOKUP returns = MON (incorrect)

So why does Excel count them correctly but doesn't return all the characters?! Very strange indeed...:confused:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi Aladin,

In case cell to look up contains word MONKEY :
LEN + VLOOKUP returns = 6 (correct)
VLOOKUP returns = MON (incorrect)

So why does Excel count them correctly but doesn't return all the characters?! Very strange indeed...:confused:

It seems the cell does not display/show the whole thing VLOOKUP returns. You probably need to widen the cell that houses the VLOOKUP formula.
 

ulsterijussi

New Member
Joined
Apr 6, 2011
Messages
9

ADVERTISEMENT

Aladin,

even with the cell as wide as my screen, the cell displays only half of characters..........

What else could be the cause?
 

ulsterijussi

New Member
Joined
Apr 6, 2011
Messages
9

ADVERTISEMENT

I'm not at work right now but I'll check tomorrow. But could it be the reason, considering numbers (for example the numbers 1234567) are returned and displayed correctly?

I'm suspecting the THREED func from morefunc addon. I'll make a test without it to see what happens..

I'll keep posting. Maybe somebody else is having the same problem.
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
Aladin is much more qualified to assist you than I am, but I would be curious as to what the following would return on a cell where only 3 of the 6 characters are displayed

=MID(your cell,4,1)
and if the result is not visible,
CODE(MID(your cell,4,1))
 

ulsterijussi

New Member
Joined
Apr 6, 2011
Messages
9
Thanks for all the ideas and propositions so far!

I played around with Format Cells, found nothing unusual and none of changes I made had an impact..

regarding West Man's idea:

=MID(your cell,4,1) didn't return anything
=CODE(MID(your cell,4,1)) returned 0

West Man; what is the function of MID? I'm not familiar with it.

Any further ideas would be greatly appreciated! Thanks!
 

ulsterijussi

New Member
Joined
Apr 6, 2011
Messages
9
Hi again,

googled MID function. Understand it now.

I tried following:

=MID('[PRODUCTIONPLANNING.xlsx]2'!$C$5;4;1)

and it returned K (4th character of MONKEY, the cell's content) so this is correct!

Can someone help me understand all of this?!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,907
Members
414,110
Latest member
docops

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
Top