find closest instance of specific value in array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Travis - 18 Oct 2006 16:37 GMT

I have an array of data where some data may appear multiple times
throughout the array. I am trying to find a way to locate the next
closest instance of a given value in an array, recognizing that the
value may appear multiple times in the array.

Example:
Array of values in A1:A10 as follows:
a,b,c,d,e,f,a,g,e,b

I want to find the location in the array that has value "b" closest to
cell A4 in the array. It should return location A2.

Any help would be much appreciated!

Travis

Reply to this Message

Bernie Deitrick - 18 Oct 2006 17:52 GMT

Travis,

Enter this using Ctrl-Shift-Enter in cell B4:

=IF(OFFSET(A4,MIN(IF(A1:A10="b",ABS(ROW(A1:A10)-ROW(B4)))),0)="b",ADDRESS(ROW(A4)+MIN(IF(A1:A10="b",ABS(ROW(A1:A10)-ROW(B4)))),COLUMN(A4)),ADDRESS(ROW(A4)-MIN(IF(A1:A10="b",ABS(ROW(A1:A10)-ROW(B4)))),COLUMN(A4)))

If you want to enter a value into a cell to look for it, use this version (Again, array entered):

=IF(OFFSET(A4,MIN(IF(A1:A10=C1,ABS(ROW(A1:A10)-ROW(B4)))),0)=C1,ADDRESS(ROW(A4)+MIN(IF(A1:A10=C1,ABS(ROW(A1:A10)-ROW(B4)))),COLUMN(A4)),ADDRESS(ROW(A4)-MIN(IF(A1:A10=C1,ABS(ROW(A1:A10)-ROW(B4)))),COLUMN(A4)))

with a b entered into cell C1.

Note that this requires that a b appear in cells A1:A10, or it will return $A$4 erroneously. That
can be fixed, but....

HTH,
Bernie
MS Excel MVP

>I have an array of data where some data may appear multiple times
> throughout the array. I am trying to find a way to locate the next
[quoted text clipped - 11 lines]
>
> Travis

Reply to this Message

Lori - 18 Oct 2006 17:54 GMT

Not sure how you set up the criteria but suppose the lookup value "b"
is in cell B2. For the nearest matching row to A4 try the array formula
(Ctrl+shift+enter to execute):

=MATCH(1,(A1:A10=B1)*((ROW(A1:A10)-ROW(A4))^2=MIN(IF(A1:A10=B1,(ROW(A1:A10)-ROW(A4))^2))),0)

> I have an array of data where some data may appear multiple times
> throughout the array. I am trying to find a way to locate the next
[quoted text clipped - 11 lines]
>
> Travis

Reply to this Message

Rate this thread: