CODEWITHSUNDEEP
excelexcel-formulaexcel-2021
WarCry WILD RIFT
WarCry WILD RIFT

Reputation: 3

find closed match value in matrix and return its row and column header in excel

enter image description here

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

Upvotes: 0

Views: 83

Answers (4)

Tom Sharpe
Tom Sharpe

Reputation: 34210

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)))))

enter image description here

Upvotes: 1

nkalvi
nkalvi

Reputation: 2614

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)))

Result

Conditional formatting for fun:

Conditional formatting for the requirement

Upvotes: 1

Michal
Michal

Reputation: 5848

enter image description here

=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))

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36870

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))))

enter image description here

Upvotes: 0

Related Questions