Oh ! I can see my mistake ... thank you very much for help me Regards,
Jorge Rodríguez ----- Mensaje original ----- De: "david_lynch" <david_ly...@blueyonder.co.uk> Para: users@global.libreoffice.org Enviados: Lunes, 24 de Junio 2013 1:30:25 Asunto: Re: [libreoffice-users] It would [not!] be a bug 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+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
