excelexcel-formula
find closed match value in matrix and return its row and column header in excel
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)))
Conditional formatting for fun:
- How to border cells based on where the user click?
- Difference between running a userform in Visual Basic Editor (F5) and opening it with Show?
- VBA delete row working in one for loop but not another
- How to get HEX color value from cell color?
- Remove columns if the number of rows is less than a certain number using vba Excel
- VBA Security : my vbe password is well-set, but still can be hacked by a simple code
- Adjust vertical scrollbar range in Excel userform listbox
- Delete user ribbon from file
- Excel Hide/Show all tabs on Ribbon except custom tab
- Partial value font color in red using vba
- How do I conditionally append a row of Excel data from one sheet to another?
- Efficient formula or code to determine number of errors in range VBA
- Rank numbers including NA()s in Excel 365
- Is it possible to have a named range attached to "activesheet," rather than a specific sheet?
- How to calculate YoY Growth without a Date Field in Pivot?
- Excel hyperlinks 'Can't open specified file' on Mac
- Is it possible to dynamically change a cell/column reference in an excell formula based on another cell
- Excel - create a list from a data column but ignore/remove blank cells
- Split text string in Excel by semicolons and space
- Formula in Excel to count occurrences of substrings within strings
- find closed match value in matrix and return its row and column header in excel
- VBA to allow users to format cells on locked worksheet
- How can my For Each Loop loop to the next row?
- Excel - Return value based in two conditions
- Power Query, M language, Count items in a row with Special condition
- excel group column highlight matching values
- VBA - Save email object (OLEFormat) to file from clipboard
- excel vba projects not closing
- Excel VBA variable Chart Range
- Changing the Date Format for GROUPED dates in a PIVOT TABLE