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

[ulsterijussi]

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

 

[Aladin Akyurek]

What does this...

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

return?

 

[ulsterijussi]

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...

 

[Aladin Akyurek]

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...

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]

Aladin,

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

What else could be the cause?

 

[Aladin Akyurek]

Aladin,

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

What else could be the cause?

Would you check Alignment and Font Tabs under Format Cells for anything unusual?

 

[ulsterijussi]

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]

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]

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]

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?!