Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


 On 23/06/2013 23:30, Brian Barker wrote:
At 12:08 23/06/2013 -0600, Jorge Rodríguez wrote:
... this other chart No. 2:

Oficina               Ventas
Ala                    18
Here                  16
Punta                 3
Total Resultado   37

I've used VLookUp function to get data from chart No 2 to this other chart:
Function: =BUSCARV(A3;'Tabla dinámica_Hoja2_1'.$A$11:$B$15;2)

             Show   Should Show
Office     Sells   Sells
Ala         18       18
Punta      3         3
Here       16       16
Cart        18       0
Lim         16       0

As you can see, the chart is order by office but I got 18 and 16 in "Cart" and "Lim" but It should show 0 in both. The LO program get datas from "Ala" and "Here" for "Cart" and "Lim". What happens?

There is no bug - but two mistakes. As you know, if you omit the optional fourth parameter in the VLOOKUP() function, the data is assumed to be sorted. But as the help text makes clear, a value is always returned in this case - corresponding to the the last value smaller than the criterion. In your case, "Ala" is next before "Cart" and "Here" is next before "Lim". To prevent this, you need to add FALSE or 0 as the fourth parameter to VLOOKUP().

Second, there is no magic process by which Calc will guess that you wish for a zero value when no match is found: there is simply no value to return! The value #N/A will be returned instead. If you want zero values here, you need to test for this error value and replace it with zero. You can do this using the ISNA() function, using something like:

=IF(ISNA(VLOOKUP(Xn;<array>;2;0));0;VLOOKUP(Xn;<array>;2;0))

or, more simply, (new in LO 4):
=IFNA(VLOOKUP(Xn;<array>;2;0);0)



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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.