[Solved] VLOOKUP Retrieves Data from Incorrect Row

Discuss the spreadsheet application

[Solved] VLOOKUP Retrieves Data from Incorrect Row

Postby White Phoenix » Sat May 29, 2010 6:28 pm

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.5
User avatar
White Phoenix
 
Posts: 205
Joined: Tue Jan 01, 2008 7:10 am

Re: VLOOKUP Retreives Data from Incorrect Row

Postby Zizi64 » Sat May 29, 2010 7:41 pm

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; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 7988
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VLOOKUP Retreives Data from Incorrect Row

Postby acknak » Sat May 29, 2010 8:41 pm

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
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: VLOOKUP Retrieves Data from Incorrect Row

Postby White Phoenix » Sun May 30, 2010 7:39 am

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.5
User avatar
White Phoenix
 
Posts: 205
Joined: Tue Jan 01, 2008 7:10 am

Re: [Solved] VLOOKUP Retrieves Data from Incorrect Row

Postby MrProgrammer » Sun Jun 06, 2010 5:07 am

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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3720
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] VLOOKUP Retrieves Data from Incorrect Row

Postby White Phoenix » Sun Jun 06, 2010 8:27 pm

Good idea. Thanks. That’s something else I hadn’t thought of.
Apache OpenOffice 4.1.5
User avatar
White Phoenix
 
Posts: 205
Joined: Tue Jan 01, 2008 7:10 am


Return to Calc

Who is online

Users browsing this forum: cristopher and 15 guests