Error using INDIRECT in a LOOKUP function

[joe.aquilina.92]

I am having a problem in using INDIRECT in a LOOKUP function in Calc 4.1.3.2 in Linux (Kubuntu 13.10).

In my worksheet, I have column of values (0-255) in column A, in the cell range A3:A258. Column B has a corresponding set of values (which are image grey scale pixel counts in this case).

From this data, I want to do some anaylsis based on maximum and minimum values in column B. However the nature of the data is that can be more than one maximum/minimum in the data in column B so I want to set a range of cells to find local maximum/minimum valies, rather than simply looking at the entire range of cells B3:B258. For example, if I know that there is a local maximum around cell B200, then I want to set the search range to B180:B220 or similar.

Once the local maximum/minimum has been found (that part of the process works just fine), and is placed into cell B268, I want to use that value in my LOOKUP to tell me what the corresponding grey scale value in column A is.

The formula I am trying to use looks like this:

=LOOKUP(B268,INDIRECT(“B”&B266):INDIRECT(“B”&B267),INDIRECT("$A$"&B266):INDIRECT("$A$"&B267))

where:
cell B268 holds the local maximum/minimum value,
cell B266 holds the lower bound of the row in the range of cells in column B where the max/min value is,
cell B267 holds the upper bound of the row in the range of cells in column B where the max/min value is

So I want the above function to effectively translate for example into:

=LOOKUP(B268;B180:B220;$A$180:$A$220)

The absolute cell references are necessary for column A as there are multiple data columns which will change as I copy the formula across into the data columns but they will all be matched against column A.

I hope that all makes sense.

When I try to use the above formula, I get a #N/A error and don’t know why. What am I doing wrong? Any advice would be helpful.

Thanks in advance and sorry if this question is a bit long.

[Lupp]

LOOKUP() will expect a data range for “looking up” sorted in ascending order. The help text doesn’t mention it. It should be of no meaning whether you use INDIRECT() or a direct reference.

Use MATCH() and INDEX() instead.

More details in the attached example. ask35865LookupError002.ods

[begin EDIT1] SORRY! I fell back into old errors commenting the above mentioned formula INSIDE my example. Reference1:Reference2 makes correct use of the infix operator “:” .
Therefore the colon may well occour between two calls of INDIRECT() . I will replace the wrong attachment. To be clear: I do not withdraw the suggested solution. [end EDIT1]

[joe.aquilina.92]

Thanks for the response, I will have a look at your suggested solution when I have some time. I hadn’t thought about MATCH() and INDEX() yet; I am still relatively inexperienced in using Calc.

The data in the columns I have been using to do the LOOKUP is not sored into ascending order, but from what I can see, the LOOKUPS I have been performing on the whole of the data range have worked. It seems that I have been lucky so far. I will be more careful in future.

Thanks again.

[Lupp]

Considering my (rather few) experiments with LOOKUP() I also experienced cases of correct results despite a not sorted LookupRange. I tend to avoid functions of unpredictable behaviour. Please note: HLOOKUP() and VLOOKUP() are not restricted in the SAME way. There are other restrictons due to which I don’t use these functions.