I'm trying to return the row header and column header based on the value I'm looking for in matrix . How can I find the lower closed match on matrix and return its row and column header in single cell? I tried reverse look up formulas and index match formula but I couldn't get any results.
by the way i'm using excel 2021 version
Looking for the lower value closest to the given.
With LET (I think it is available in Excel 2021):
=LET(
look_for, K18,
in, B2:I31,
closest, MAX(IF(in <= look_for, in, 0)),
closest_col, MAX(IF(in = closest, COLUMN(in), 0)),
closest_row, MAX(IF(in = closest, ROW(in), 0)),
INDEX(A:A, closest_row, 1) & ", " & INDEX(1:1, 1, closest_col)
)
Without using LET
=INDEX(A:A, MAX(IF(B2:I31 = MAX(IF(B2:I31 <= K18, B2:I31, 0)), ROW(B2:I31), 0)), 1)
& ", " & INDEX(1:1, 1, MAX(IF(B2:I31 = MAX(IF(B2:I31 <= K18, B2:I31, 0)), COLUMN(B2:I31), 0)))
Conditional formatting for fun:
=LET(
reference,L1,
array,B2:I12,
tocol,TOCOL(array),
cols,COLUMNS(array),
closest,XLOOKUP(MIN(ABS(tocol- reference)), ABS(tocol - reference), tocol),
position,MATCH(closest,tocol),
xpos,MOD((position-1),cols)+1,
ypos,INT((position-1)/cols)+1,
VSTACK("Step " & xpos, "Level " & ypos))
In Excel 2021, you could do it in a similar way to this answer
In N2:
=M2-MIN(IF(B2:K11<M2,M2-B2:K11))
(or much better would have been =MAX(IF(B2:K11<=M2,B2:K11)) like @nkalvi)
In O2:
=LET(rData,B2:K11,rCol,B1:K1,rRow,A2:A11,match,N2,
rowSeq,SEQUENCE(ROWS(rRow)),colSeq,SEQUENCE(1,COLUMNS(rCol)),
INDEX(rRow,LET(x,(rData=match)*rowSeq,MIN(IF(x>0,x)))))
In P2:
=LET(rData,B2:K11,rCol,B1:K1,rRow,A2:A11,match,N2,
rowSeq,SEQUENCE(ROWS(rRow)),colSeq,SEQUENCE(1,COLUMNS(rCol)),
INDEX(rCol,LET(x,(rData=match)*colSeq,MIN(IF(x>0,x)))))
You have to specify what closed value do you want, higher or lower. Suppose you want exact match or higher value then try following formula to column header.
=LET(x,XLOOKUP(K6,TOCOL(B2:I31),TOCOL(B2:I31),,1),
INDEX(A1:I1,1,MAX(COLUMN(B2:I31)*(B2:I31=x))))
And for row header-
=LET(x,XLOOKUP(K6,TOCOL(B2:I31),TOCOL(B2:I31),,1),
INDEX(A1:A31,MAX(ROW(B2:I31)*(B2:I31=x))))
=LET(x,XLOOKUP(K6,TOCOL(B2:I31),TOCOL(B2:I31),,-1), INDEX(A1:I1,1,MAX(COLUMN(B2:I31)*(B2:I31=x))))