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