[Solved] LOOKUP function always returns #N/A

[Nox]

Forgive me if I have missed the answer to this question in my search. I've searched the forums & checked the help topics in OOo as well as the function reference link at the top. I have checked my syntax over and over and LOOKUP still does not seem to be working for me even though I think it is correct.

I am running Windows Vista 64 and OOo 3.0.1 (just updated it 2 nights ago to make sure I was on the latest version). I am coming to this forum because I think either I am missing something basic in my LOOKUP function call or maybe lookup is not what I need. So here's my scenario:

I have a multiple page spread sheet. The first page is a form where a user would fill in certain cells on the form (some of them are merged). Based on what they fill out, I need to have another cell perform a lookup against a column of data and return the appropriate value that is contained in a corresponding column.

Code: Select all

=IF(E8<8;"";LOOKUP(E8;Data.A25:A35;C25:C35))

The logic (at least in my head) is that it will look up the contents of E8 and compare it against the data in the cells A25 through A35 on the sheet labeled Data and return the corresponding value from cells C25 through C35.

The IF portion of the formula works fine. If I take out the IF portion of the formula I still get #N/A as the result. I have verified that the value I am putting into cell E8 can be found in the range of cells A25 to A35 on the Data worksheet using the find feature.


Is there a program option I have turned on (or off) that shouldn't be? Maybe a cell formatting problem (text instead of number, etc)? Is LOOKUP what I should be using for what I want? I have used excel in the past with the LOOKUP function and also in earlier versions of OOo. However, the only difference I can see in this is that I created this file brand new in OOo instead of converting it from excel, which I have done in the past. Any help would be greatly appreciated, though I suspect I may be a little embarrassed for missing something simple.

[FJCC]

One way you could get that error is if the contents of E8 are greater than or equal to 8 but smaller than any value in A25:A35. Lookup cannot make a match, so it looks for the largest value in A25:A35 that is smaller than E8, but none of them are smaller and you get an #N/A. Could that be the problem?

[Villeroy]

LOOKUP, MATCH, VLOOKUP and HLOOKUP work like they use to work in most other spreadsheet applications of the past 2 decades.
How to find data in an Excel table[Microsoft]
How to use the LOOKUP function with unsorted data in Excel[Microsoft]
http://projects.gnome.org/gnumeric/functions.shtml
Calc Functions
Re: [Solved] LOOKUP merged cells problem (has nothing to do with merging)
Re: Reference rows in 1 sheet and offset in another
Re: [Solved] How to get ALL values matching a criteria
I'm Stumped - Some sort of lookup/spreadsheet issue?
LOOKUP problem...

LOOKUP works for spreadsheets. If you use a spreadsheet as a database you have to use other methods, all of which are cumbersome since a spreadsheet has nothing in common with a database when it comes to unique items related to other table's items.

[keme]

Most likely, the reason for #N/A returned is that the lookup range is not sorted.

LOOKUP() requires a sorted list. With unsorted lists the lookup may work, but it's not reliable.
Other lookup functions, VLOOKUP(), HLOOKUP(), and MATCH(), have an optional parameter to indicate that the list is not sorted by the search field.

Using MATCH() with INDEX() provides more or less the same functionality as the LOOKUP() function. When the return value is in the same table as the value to look up (as it seems to be in this case), VLOOKUP() is probably better.

[Nox]

Thank you all for your replies, while they don't solve the problem I am having, it actually gives me some good resources I didn't find before.

Well I was at work and did some additional testing. The problem appears to not be with the function or the results, but with the spreadsheet itself. If I create a blank sheet and retype all of the data including the formula, it works fine. If I export the existing worksheets to CSV (to strip all the formatting) and re-import it, I can put in the same formula using the existing data and the LOOKUP performs just like I was expecting it to.

So now the question is, what could I have done to cause this? I don't remember doing any sort of strange formatting (aside from basic cell merging and font changes), but now I'm curious. While this spreadsheet is for personal use, I'd hate to do it to one for work.

[Villeroy]

=IF(E8<8;"";LOOKUP(E8;Data.A25:A35;C25:C35))

You import the data from some text-source or clipboard. The results are supposed to be numbers, but actually it's text. In this case, the source of the problem could be quoted figures in the data source (For instance "12.123456789" because Excel is not able to dump floating point numbers correctly into csv. Of course, the rest of the IT world has to adjust everything to this Excel-bug).
Unquoted 12.123456789 will not be a number if your spreadsheet assumes commas as decimal separator. 12.123 may be an number then but the wrong one (12 thousand one hundred twenty three).

You see the value of E8 (which is supposed to be a number since you compare it with 8) in A25, but when you test =A25=E8 the result is FALSE? One of the values could be text, if all values are numbers column A starts with high numbers and you don't know why all documentation on lookup stresses the requirement of a sorted search vector?

You copy down the LOOKUP formula and did not notice that the formula in the next row changed to =IF(E8<8;"";LOOKUP(E9;Data.A26:A36;C26:C36)) ?

[Nox]

Villeroy, if I understand you correctly, it sounds like when I converted all the cells in my Data worksheet to text originally, this caused the problem?

After your post I remembered I had a bunch of text that kept converting to dates (5/10/15, 3/6/9, etc...), so rather than selectively fix those cells I was lazy changed the cell format of the entire worksheet to text, since the entire worksheet was simply numbers or text anyway. I figured that a number stored as text wouldn't matter. 8 is the same as 8, regardless if it is stored as a number or text, right? It seems I was wrong about that. There was a point I thought maybe that was the issue, but even when I changed the formatting back to a number, I was still getting #N/A. I'll probably never know for sure, but I'll be a bit more mindful about cell formatting in the future.

I am also going to look into using VLOOKUP, even though all my lists so far are currently sorted. Thanks everyone for your help!

[Villeroy]

Nobody will ever be able to tell what went wrong without knowing any of your import data and your spreadsheet locale (the operating system's locale by default). One thing is certain: ="123"=123 always returns FALSE. When sorting "12" is lower then "9" in alphabetical sort order, but number 12 is higher than number 9. This is exactly the same difference in any software product (OK, Excel since 2003 tries to be extra-"clever" trying to please extra-stupid users).

Importing 12.3 will put twelve-point 3 into English spreadsheets, this year's 12th of March in to German spreadsheets and 12 hours, 3 minutes into a Italian spreadsheets, just as if you type 12.3 manually into the same spreadsheet.
When importing plain text representing English figures you need to (temporarily) adjust the office locale or specify "US English" as column type in the import dialog. The software can not read your mind. It treats all input in the set up linguistic context (the OS locale if nothing is specified).

The logic (at least in my head) is that it will look up the contents of E8 and compare it against the data in the cells A25 through A35 on the sheet labeled Data and return the corresponding value from cells C25 through C35.

This is NOT what LOOKUP does, not even with corresponding types of values.

=IF(E8<8;"";LOOKUP(E8;Data.A25:A35;C25:C35))
=IF(E8<8;"";VLOOKUP(E8;$Data.$A$25:$C$35;3;0))