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
WarCry WILD RIFT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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))
2
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))))
4