[libreoffice-users] libreoffice-users] VLOOKUP & COUNTIF with numbers and text

libreoffice-users] VLOOKUP & COUNTIF with numbers and text

"Phil Hibbs" <snarks@gmail.com>
To: 
"" <users@libreoffice.org>, "" <users@openoffice.org>
VLOOKUP and COUNTIF will not find a numeric search value in a list of text
cells. They will, however, find a text value in a list of numeric cells. Why
one way but not the other? I am not a fan of strong typing in end-user
applications (it's fine for programming languages, but not all spreadsheet
users have a programmer's mindset or dilligence).

At least VLOOKUP and COUNTIF are mutually consistent - Excel will not work
either way in VLOOKUP, but works both ways (text in a list of numbers, and
number in a list of texts) in COUNTIF.

Having said that - if the list contains the text being looked for and the
same value as a number, then COUNTIF as text will find both and return 2. If
looking for it as a number, it only finds one of them. So, is 1 in the list
once or twice? That depends on how you look. Not ideal. This inconsistency
raises this behaviour from a "feature" to a "bug" in my opinion.

Phil.

Phil,
I was able to duplicate your result.  I think the best solution might be to 
limit the users input to one data type, i.e. values, using conditional 
formatting. Another solution would be to add a column to your spreadsheet that 
converts the column of values and text into only values (type 1) using the value 
function. Then do your vlookup and countif work from this new column. It would 
eliminate the possibility of getting different answers from searching two 
different data types.  


Regards,
Ron

-- 
E-mail to users+help@libreoffice.org for instructions on how to unsubscribe
List archives are available at http://www.libreoffice.org/lists/users/
All messages you send to this list will be publicly archived and cannot be deleted