Problem getting VLOOKUP in Oo Calc to Work

  alternator 21:09 28 Oct 2009

Am using Oo Calc Version 3.1.1 on WinXP sp3 machine.
I have posted on Oo forum but no joy there was hoping someone here could resolve my problem.

I have a table whose array consists of 11 columns and 100 rows. I am using VLOOKUP to return values from the table. When using indexes up to the value of 4 it returns the value perfectly. However as soon as I put in an index value greater than 4, it returns a blank cell, all other parameters being the same. See example below.
=VLOOKUP(1;'Order details'.$B$38:$T$142;4;0) returns a valid value, however
=VLOOKUP(1;'Order details'.$B$38:$T$142;5;0) returns a blank cell even though the cell referred to has a value in it.
The value in the cell refered to by index 4 is a manually entered text string
The value in the cell refered to by index 5 is a manually entered numerical value
The value in the cell refered to by index6 is numerical value returned by the formula in this cell
The final value I want to retrieve is the value returned by index 10, the cell referred to by this index also is numerical value returned by the formula in this cell.
Any suggestions gratefully recieved.
Just another bit of info if it helps. If the =VLOOKUP(1;'Order details'.$B$38:$T$142;4;0)is pasted into the cells where I am using index 5,6 &10 it returns the value from the 4th colum without problem, so it does seem to be a formatting problem. I am at a loss.
Further attempts: I have tried moving the position of the cells with the VLOOKUP formula in to another point on the sheet, again with the same result. I have also tried typing the entry, cutting and pasting, and dragging the handle with same outcome. formulas using index value greater than 4 return a blank cell (Not getting error message in the cell)

  alternator 21:26 28 Oct 2009

Playing with the table I have noticed that the values referred to by index 6 and 10 are formatted to show currency but when I try to refomat them as plain digits they seem to be stuck as currency.

  alternator 22:08 28 Oct 2009

Ok guys sorry to all those that had to read all this. But I have discovered my errors, hopefully it might help someone in the future. I had merged some of the cells which obviously threw the VLOOKUP navigation off, and somehow some of the columns were hidden, which also had the same result, appologies for wasting all your time.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?