finding the cell that is closest to the current ce

Question

I have a row of values - 1,2,3,8,35,7,8,3,5,7,x

X is where I want the formula to be

I'd like to somehow get the value of the row which 8 is the closest to X (so not row 4 in this case, but row 7)

If I use match("8",A:A,0) I get the first match it finds.

How do I find the closest match to the cell where the calculation occurs?

Answer 1

You may use:

{=MATCH(2,1/(A1:A10=8))}

just remember it is an array function, so CTRL+SHIFT+ENTER must be used

The answer is based on a trick and behaviour of the MATCH function. I will allow myself to copy the explanation from ozgrid:

The magic here is actually in the MATCH function more than the array. Two interesting properties of the match function are being used here:

1) With MATCH, if no match is found, then the function will return the position of the last value in the array, so if you did =MATCH(8,{1,2,3,4,5,6,7,6,5,4,3,2,1}), your result is = 13, since there was no 8 to find in the array

2) MATCH will return the position of the last value, but won't return the position of an error (or of a blank value), so if you have =MATCH(8,{1,2,3,4,#DIV/0!,#DIV/0!,7,6,5,4,3,#DIV/0!,#DIV/0!}), your result is = 11, as the 3 in the 11th position is the last value in the array

So daddylonglegs' formula checks each cell against the target value with (A1:A13=B1) in the array formula, giving an array with TRUE (or 1) for the positions where the cells match, and with FALSE (or 0) for the rest. Dividing 1 by this results in 1s wherever that array was TRUE, and #DIV/0! wherever that array was false. So his formula is evaluated as

=MATCH(2,{#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!})

Since no '2' is found in the array, and the last value (1) is found in the 9th position, the MATCH returns 9

Answer 2

If you want the last match you can use this formula in A11

=MATCH(2,INDEX(1/(A1:A10=8),0))

[Note: if the values are numeric then 8 is OK - if they are text formatted numbers then you need "8"]

....or do you have values either side of the calculation cell (so nearest might be up or down)?