The slice function extracts a clean subset from the main table. It finds where your value appears in the table. From that spot, it moves up, down, left, or right based on the offsets you give.
Named Function Formula:
L122:N131 is [table range]
"md5aeox" is [lookup value]
-2 is [row offset]
1 is [column offset]
Named Function Formula:
=ARRAYFORMULA(
LET(firstCol, INDEX(range,,1),
found_r, MIN(ARRAYFORMULA(IF(firstCol = lookup_value, ROW(firstCol) - MIN(ROW(firstCol)) + 1, ""))),
found_c, 1,
start_r, MAX(1, IF(row_offset < 0, found_r + row_offset, found_r)),
end_r, MIN(ROWS(range), IF(row_offset < 0, found_r, found_r + row_offset)),
start_c, MAX(1, IF(col_offset < 0, found_c + col_offset, found_c)),
end_c, MIN(COLUMNS(range), IF(col_offset < 0, found_c, found_c + col_offset)),
height, MAX(0, end_r - start_r + 1),
width, MAX(0, end_c - start_c + 1),
result, CHOOSEROWS(CHOOSECOLS(range, SEQUENCE(1, width, start_c)), SEQUENCE(height, 1, start_r)),
result + NOW()*0
)
)
Named Function Arguments:
range
lookup_value
row_offset
col_offset
Example Usage:
=SLICE(L122:N131,"md5aeox",-2,1)
where,L122:N131 is [table range]
"md5aeox" is [lookup value]
-2 is [row offset]
1 is [column offset]