Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index

At 09:44 19/11/2015 -0600, Jorge Rodríguez wrote:
I did this in my workbook:
Sheet 1            Sheet2
1st Matrix 2nd Matrix VlookUp Result
Name        Name                                                       Formula
0 A.D. 0 A.D. Empires Ascendant =BUSCARV(A2;Hoja1.A3:A6;1;1) 0 A.D. 2HFU 3D Chess =BUSCARV(A3;Hoja1.A3:A6;1;1) 2HFU 3D chess Abe =BUSCARV(A4;Hoja1.A3:A6;1;1) 3D chess

Hold on! As I made very clear in my last message (and you even copied in yours!), "If, as here, there may not be a match, you need to set the SortOrder parameter to FALSE or zero, irrespective of whether the list is sorted." You have set it to 1 and so are telling the function that there *will* be a match somewhere in the array. I if there is no match, you will get wrong results. That's all explained clearly in the help text.

You must have typed those three formulae separately, which is a recipe for errors. You need the array to be specified not as Hoja1.A3:A6 but as Hoja1.A$3:A$6. That way, you can fill the formula down the column. Surely that idea was explained on page 3 (or thereabouts) of your Beginner's Guide to Spreadsheets?

In the first case I tried to find "0 A.D. Empires Ascendant" from 2nd Matrix - sheet 2- in the 1st Matrix -sheet 1- and found "0 A.D." ... it was good !

Actually, you have been satisfied by a rogue result. The function searches for "0 A.D. Empires Ascendant" and fails to find it. With the correct fourth parameter, you would see that.

But in the second case I tried to find "3D Chess" that is the third element in the first Matrix-Sheet 1- and the found result is "2HFU" ... it isn't good and confused me.

You did not take my advice - and instead told the function that a match was guaranteed. Since it wasn't - "3D Chess" and "3D chess" do *not* match if case is relevant - you got a wrong result. Set the fourth parameter correctly and you will see the correct result.

In the last case I tried to find "Abe" and found "3D chess" more confuse to me.

Same problem.

And if I used "=BUSCARV(An;Hoja1.A3:A6;1;0)" with zero at end, the tree results are "#N/D" ("An" is for not to repeat tree times the formula)

That means that the result is "not available" - in other words that there is no match. That is surely what you want to see in that circumstance? (Note that there is no exact match for any of your three examples.)

I don't know if I'm doing something wrong or there is a bug.

I do!

Some time ago, VlookUp was using only to search exactly element (Only equal) now it has so strange behavior (At least to me)

It is doing exactly what it says on the tin (er, in the help text).

I'm not sure why you keep asking for help, since you have consistently ignored my advice. That is very much your prerogative, of course, but you can hardly expect anyone to continue to help if you do so. (I note that no-one else is offering help.) In addition to the SortOrder parameter, you have clearly not understood the difference between exact matches and the fuzzy matches that you are clearly hoping for. You failed to respond to the seven example questions I asked in my message of 16th or the additional three in my message of 18th, and it is clear that you have not appreciated their significance.

By the way, the exact behaviour of VLOOKUP() is affected by options at Tools | Options... | LibreOffice Calc | Calculate. You will need to understand those.

Oh, and do you remember my suggestion that you might do this job manually? I'm pretty sure I could have done this more quickly that just trying to help you!

I trust this helps.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.