2015 Archives by date, by thread · List index

# 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

```
```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.
```
```
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
```