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.