[Solved] VLOOKUP Retrieves Data from Incorrect Row

[White Phoenix]

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

[Zizi64]

The exact formulas are:

=IF(B12="";0;VLOOKUP(B12;Items.A6:I342;6;0))
=IF(B25="";"";VLOOKUP(B25;Items.A6:I342;9;0))
=IF(B26="";"";VLOOKUP(B26;Items.A6:I342;8;0))

The last zero (boolean: false) parameter needed, if the list (Items) not an orderly queue.

See the OOo HELP. (is there Exact matching or not)
for example:
'aguav berry ' (+SPACE!) in Item list not equals to 'aguav berry'

[acknak]

I think Tibor's last advice is the key: your first column has about a dozen entries that have space(s) at the end and they won't match unless your lookup data also has the space. The "ordered" lookup that you're using (i.e. no zero parameter at the end) is probably faster, and your table is sorted, but you run the risk of getting the wrong row if the match is not exact. If you use the "exact match", un-ordered lookup (i.e. with the zero parameter), then the lookup is less efficient but you get an error if there is not a perfect match.

I'm wondering though, did this sheet come from Excel? Does Excel ignore trailing spaces in a LOOKUP like this?

[White Phoenix]

The last zero (boolean: false) parameter needed, if the list (Items) not an orderly queue.

Since it is ordered the last parameter is not needed, but that isn’t the problem, is it?

'aguav berry ' (+SPACE!) in Item list not equals to 'aguav berry'

Auggh! Those lousy spaces. OpenOffice doesn’t seem to have a setting to automatically trim trailing spaces from entries. No, this didn’t come from Excel, I only use OpenOffice. But I do copy and paste lists from the web pages. Although, usually I edit them either in Notepad or my text editor first. The text editor automatically strips trailing spaces at the ends of lines, but Notepad doesn’t.

Well, anyway now I that know what the problem is, I can fix it. I knew it had to be something simple that I was overlooking, but I couldn’t find my own mistake.

Thanks, Zizi and Acknak.

[MrProgrammer]

You can remove trailing blanks from your data with the Find & Replace dialog. Ensure that "Regular expressions" in "More Options" is enabled. Then search for " +$" (Blank, Plus, Dollar sign), replace with nothing, and use Replace All. The search expression means "the longest string of blanks at the end of the cell". This won't remove any blanks between words. Those can cause problems, too. If the table contains "aguav berry" (one space) and you look for "aguav  berry" (two spaces), it won't be found. You can collapse multiple blanks to one with a different regular expression: " +" (Blank, Plus) replacing that with with one blank. Or you can apply the TRIM() function to your data.

Removes spaces from a string, leaving only a single space character between words.

[White Phoenix]

Good idea. Thanks. That’s something else I hadn’t thought of.