Hello mrexcel community,
Unless i am completely inept at using the search function, this doesn't seem to be a redundant question, and i would be grateful for any help with solving this puzzle. The problem is performing a lookup on a function which outputs multiple values (and thereby spills into the cells below) to find the position of a value, i.e. =LOOKUP(5,ARRAY). This works fine if the array is a list of cell references, but i am struggling to make it work with an anonymous range.
...
...
I am currently trying to use a function to determine the position of a search "match" of 4 conditions. The function checks if a point is within one of several rectangles, and looks like so:
x greater than x0
x less than x0+dx
y greater than y0
y less than y0+dy
to produce an "array" of 1's for TRUE*TRUE*TRUE*TRUE and 0's otherwise. But my problem arises when i try to determine the location of the first occurence of a 1 in this "array".
If i use =LOOKUP(1,($AD...)) the result seems to always be the same (which is 0 if i try to lookup either 1 or 0), as though the "array" does not have an order of its positions unless it is allowed to become a dynamic range and spill down into the cells below it. The screenshot below shows usage of the function, and the 0 in the N column is the formula:
So i would like to pose the question; how if possible, can i determine the position of this 1 value in the "array", while keeping my formula to a single cell, that is in the case above, the fomula would become f(x,y)=2.
Unless i am completely inept at using the search function, this doesn't seem to be a redundant question, and i would be grateful for any help with solving this puzzle. The problem is performing a lookup on a function which outputs multiple values (and thereby spills into the cells below) to find the position of a value, i.e. =LOOKUP(5,ARRAY). This works fine if the array is a list of cell references, but i am struggling to make it work with an anonymous range.
...
...
I am currently trying to use a function to determine the position of a search "match" of 4 conditions. The function checks if a point is within one of several rectangles, and looks like so:
Which multiplies the four true/false expressions for:
x greater than x0
x less than x0+dx
y greater than y0
y less than y0+dy
to produce an "array" of 1's for TRUE*TRUE*TRUE*TRUE and 0's otherwise. But my problem arises when i try to determine the location of the first occurence of a 1 in this "array".
If i use =LOOKUP(1,($AD...)) the result seems to always be the same (which is 0 if i try to lookup either 1 or 0), as though the "array" does not have an order of its positions unless it is allowed to become a dynamic range and spill down into the cells below it. The screenshot below shows usage of the function, and the 0 in the N column is the formula:
So i would like to pose the question; how if possible, can i determine the position of this 1 value in the "array", while keeping my formula to a single cell, that is in the case above, the fomula would become f(x,y)=2.
