Array formula not working when trying to drag to the right

[merkwuerdigst]

This is the beast:

=INDEX(B14:B17;MATCH(MIN(ABS(C14:C17-C13));ABS(C14:C17-C13);0))

First things first: I am using Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 12; OS: Linux 6.2; UI render: default; VCL: gtk3
Locale: de-AT (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.3
Calc: threaded

Second: Above array formula is working in the cell I am executing it. When I am trying to drag the (I am using lower right corner of the cell) formula to the right, the column references don’t adjust.

ABS(C14:C17-C13));ABS(C14:C17-C13);

All the ranges and substractions stay at column C, instead of jumping to D and E etc.

Third: index reference B14:B17 are ticker symbols (like GOOG, AMZN), the other columns are numbers derived from another array formula. The array formula is looking for the “closest match” for one ticker symbol from a pool of ticker symbols.

I hope someone can point me in the right direction. Cheers.

[karolus]

@merkwuerdigst

Third: index reference B14:B17 are ticker symbols (like GOOG, AMZN), the other columns are numbers derived from another array formula

So you will first fix the Columns of B:

=INDEX($B14:$B17;MATCH(MIN(ABS(C14:C17-C13));ABS(C14:C17-C13);0))

and (with Array-formulas) you need to hold the <ctrl> key meanwhile dragging.