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 0As 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)) I trust this helps. Brian Barker -- To unsubscribe e-mail to: [email protected] 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
