libreoffice-users] VLOOKUP & COUNTIF with numbers and text "Phil Hibbs" <[email protected]> To: "" <[email protected]>, "" <[email protected]> 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 [email protected] 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
