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