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

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: 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