debugging LOOKUP()

English
, , ,
#1

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

#2

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

#3

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.

#4

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.

#5

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

#6

Data type is important - the sort order is:

  • first: sort all numbers
  • second: sort all text

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.

#7

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.

#8

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.

#9

The data range can contain mixed numeric and text types, but the data must be strictly sorted ascending, i.e. numeric before text and each within, so sorting the data range first would work. In your case you have to sort the data anyway even if it was all text (which it probably should) because it is not sorted at all. Using LOOKUP() on not strictly sorted data will yield arbitrary and wrong results.

#10

So, how can I make them text, there doesn’t seem to be an option via formatting.

Use find and replace:

  • Select the number range to be converted to text
  • Make sure it is formatted as text cells
  • Menu selection Edit - Find and replace
  • Search for .* (period + asterisk)
  • Replace with $0 (dollar sign + zero)
  • Tick Regular expressions
  • Click Replace all

The find/replace procedure will act like re-entering the numbers. When the receiving cell is formatted as a text cell, that is how the reentry is taken. One might expect that copy/paste would do the same, but I don’t believe that there is a way to have conversion by paste.

Note that LOOKUP() will return “nearest match” if no exact match is found, so you may need to double check for perfect match if there is a risk of typos. Using VLOOKUP() or MATCH()/INDEX() may yield a cleaner formula. The MATCH(), VLOOKUP() and HLOOKUP() functions have an optional last parameter to specify “unsorted search mode”, which also requires exact match. This mode of operation is not available to the original LOOKUP() function.

#11

Thanks for the response. I think it is clear from the hoops one has to go through to “textify” cell contents that this is far from intuitive.
Lookup also will not yield a useful result of the looked up value type is text but the lookup-table index value is a number. This was partly the problem which I may or may not not have had when using VLOOKUP or HLOOKUP.
The reason I used LOOKUP in the first place is because its documentation is much clearer (because it leaves out all the important information like table sort order, value types etc.).
I guess this all boils down to bad documentation and user error.

#12

[…] hoops one has to go through to “textify” cell contents that this is far from intuitive. […] I guess this all boils down to bad documentation and user error.

Quite so!

The documenttion still has a way to go to be perfect, and the distinction of data types is a matter of some discourse (albeit I don’t think the discourse will bring a material change in any foreseeable future).

It is a matter of learning to use the tool at hand, with its strengths and weaknesses, limitations and peculiarities. This place is good for that. :wink:

Different spreadsheet apps have somewhat differing levels of strictness when interpreting different data types, but the issues are largely the same across all major softwares. Some users want a stricter handling, others wish for a looser handling.

As for documentation, any user with a clear view of the software’s use and some experience could contribute. Please consider going there.

#13

Text to columns may be an answer.
See related question: Calc - How do you sort a list of numbers alphabetically? - #6 by LeroyG

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.