Hi Brian and all:
I'm really sorry by my fail using LO spreadsheet and follow yours
advises. Besides I didn't know the options of tools (LO-Calc-Calculate)
Thank you again,
Regards,
Jorge Rodríguez
El vie, 20-11-2015 a las 07:05 +0000, Brian Barker escribió:
> 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
>
>
--
Atentamente,
Jorge Rodríguez
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
