Re: [libreoffice-users] Match Items from two different matrix

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+unsubscribe@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