find closest instance of specific value in array

[Travis]

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

 

[Bernie Deitrick]

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

 

[Lori]

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)