At 09:44 19/11/2015 -0600, Jorge Rodríguez wrote:
I did this in my workbook:
Sheet 1 Sheet2
1st Matrix 2nd
Name Name Formula
0 A.D. 0 A.D. Empires Ascendant
=BUSCARV(A2;Hoja1.A3:A6;1;1) 0 A.D.
=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.
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.
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.
Impressum (Legal Info)
: 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