I have come across a problem with using VLOOKUP in Calc just recently. I haven’t found exactly the same problem.

I am using an array in my spreadsheet which is accessed by three different formulas, each for a different field index.

Only one is finding the correct information it needs. The other two wind up getting the information from the row above. The only time the other two formulas work is when I change the index number to the same as the one that works. The array is sorted in ascending order, otherwise the second formula would not work. For some reason, I cannot find a way to get the other two to access the correct record.

The three formulas are:

=IF(B12="";0;VLOOKUP(B12;Items.A6:I342;6))

=IF(B25="";"";VLOOKUP(B25;Items.A6:I342;9)) [This is the one that works.]

=IF(B26="";"";VLOOKUP(B26;Items.A6:I342;8))

Here is a link to the array that I am using: http://provide.net/~rodlockwood/Items.ODS