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:=($AD$16>V29:V40) * ($AD$16<V29:V40+X29:X40) * (W29:W40<$AE$16) * (AE16<W29:W40+Y29:Y40)
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:
=LOOKUP(1;(Indata!$AD$16>Indata!V29:V40)*(Indata!$AD$16<Indata!V29:V40+Indata!X29:X40)*(Indata!W29:W40<Indata!$AE$16)*(Indata!AE16<Indata!W29:W40+Indata!Y29:Y40))
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.