## [Solved] VLOOKUP Retrieves Data from Incorrect Row

### [Solved] VLOOKUP Retrieves Data from Incorrect Row

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
Last edited by White Phoenix on Sun May 30, 2010 7:41 am, edited 2 times in total.
Apache OpenOffice 4.1.7 on Windows 7 Professional.

White Phoenix

Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

### Re: VLOOKUP Retreives Data from Incorrect Row

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'
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 9704
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: VLOOKUP Retreives Data from Incorrect Row

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?
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### Re: VLOOKUP Retrieves Data from Incorrect Row

Zizi64 wrote: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?
Zizi64 wrote:'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.
Apache OpenOffice 4.1.7 on Windows 7 Professional.

White Phoenix

Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

### Re: [Solved] VLOOKUP Retrieves Data from Incorrect Row

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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3864
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: [Solved] VLOOKUP Retrieves Data from Incorrect Row

Good idea. Thanks. That’s something else I hadn’t thought of.
Apache OpenOffice 4.1.7 on Windows 7 Professional.

White Phoenix

Posts: 222
Joined: Tue Jan 01, 2008 7:10 am