Get index/position of value in dynamic range

nicklasx

New Member
Joined
Oct 26, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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:

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

=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))

1635284827645.png


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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Welcome to the Forum!

Try: =MATCH(1,($AD$16>V29:V40)*($AD$16<V29:V40+X29:X40)*(W29:W40<$AE$16)*(AE16<W29:W40+Y29:Y40),)

Although as you note, this will find only the first occurrence.
 
Upvote 0
Solution
One reason that your LOOKUP formula would be failing is indicated by this extract from the LOOKUP function help
Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value.
 
Upvote 0
Seems i either missed or was unable to view post #2 at the time of my last reply, but using MATCH did exactly what i needed.

Thank you for the help everyone!
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top