Re: [libreoffice-users] It would [not!] be a bug

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

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