debugging LOOKUP()
Hello,
I have a sheet (price) which acts as a lookup table for another sheet where I use the LOOKUP function. This mostly works, but for one row in the sheet it is used in, it does not. The column B contains a text.
This works:
=LOOKUP(B10,$Prices.A$1:$Prices.A$206,$Prices.B$1:$Prices.B$206)
The next row does not (it yields #N/A)
=LOOKUP(B11,$Prices.A$1:$Prices.A$206,$Prices.B$1:$Prices.B$206)
All following rows work again as do all above B10. The value which is being looked up definitely is contained in the lookup sheet and the result value for that index is a number.
On a potentially related note I discovered a sorting issue that I can't explain. In order for the LOOKUP function to work, the lookup table must be in ascending order. However, when I alpha-sort this table (this is the index column of the mentioned table), Calc somehow gets things wrong. Take a look:
3205
3206
6002
6003
6004
6005
6006
6007
6008
6009
6010
6204
6205
6206
6304
6305
51104 <------
51105 <------
51108 <------
51202 <------
51205 <------
2344 P4 <------
51106 <------
51109 P4 <------
6303 <------
7204 B
7205 B
7206 B
7208 B P4
7208 B P5
7211 B P5
7304 B
7305 B
NA 4906
NN 3010K P41
NN 3012K P41
NU 208 P42
I have no idea what the reason for this is. The column format is "Text". When I copy/paste this list from a text file into a new sheet, then sort the order is correct.
This is driving me nuts, can someone please tell me what I'm doing wrong?
Thanks Mark
.jpg "keme"){kind=small}
.jpg "keme"){kind=small}
Have you checked with
View -> Value Highlighting, if everything really is text or do you get blue numbers (format of the column doesn't tell the full truth)?Thanks for the tip, indeed I get mixed value types. So, how can I make them text, there doesn't seem to be an option via formatting.
No - formatting never changes the data type - its a pure representation thing. Formatting in advance (i.e. before entering data) as Text does disable data / value recognition. But once the data type is determined you can't change by simply formatting.
OK, but then the data type should not be responsible for the sort order, since I can create two different orders with the exact same data depending on how I input the text (typing vs. copying from the clipboard which triggers the table-import dialog).
Data type is important - the sort order is:
and copying is bypassing value recognition - you get what is in your original cells (the clipboard does not contain only the values but everything, incl formatting of the cell copied,) while the import dialog does value recognition, hence you get different data types from both procedures and in consequence a different sort order.
So, a column with mixed data types cannot exist, at least not for looking up stuff? I doubt that is the case. See my previous comment, same data, different types, somewhere there must be a way to define this.
Actually, when going through the import dialog, the data types seem OK, because the sort order is. Typing manually does not work.
So, when I re-enter the numbers (recognized as numbers, blue) into the column, they are now recognized as text (black), presumably because the column format is text (which I changed after entering the numbers manually). I now used "Text to columns..." on the typed list of entries and it converted all recognized numbers into text. Now the lookup works.
It seems odd that there should be no way to directly influence the value type of a cell, though.