Search code examples
excelexcel-formulaexcel-2021

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

Solution

  • 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