I have a table like this:
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | 2 | 3 | 3 | 5 |
| 5 | 1 | 3 | 2 | 3 |
| 2 | 2 | 1 | 3 | 3 |
For a given number in the table I want to have get the letter corresponding to the column of the last appearence of the number in some row.
For example 1 will return C or 2 will return D.
It seems like XLOOKUP is a good start, but I first need to "flatten" the table into a single row to look into.
Any ideas on that?
Use this formula : =MAX(IF(A1:E3=G3, COLUMN(A1:E3)))
This will give, for example, the column number 4 (D) when
searching for the value 2 (stored in G3):
Explanation : The IF function searches for an equality to the value, returning the column number for all matches. The MAX function then selects the highest column number among the matches.
You can use this formula that will return the Column Letter.
=SUBSTITUTE(ADDRESS(1,MAX(IF(A1:E3=G1,COLUMN(A1:E3))),4),1,"")
In older version of Excel you may need to press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. Formula will enclose in Curly Braces to indicate it's an Array Formula, as seen in below screenshot. This step won't be required on 2021 or 365.
If ; is the list separator for your regional settings, replace comma , with ; semi colon
