Search code examples
excelindexingrow

Is there any Excel function that can copy column's (first row's) name if the value matches?

I can't figure out a function that would automatically copy the first row value if the value on another rows matches 1. I would need the red dates to be automated.

Any advice would be very appreciated, thank you!

Excel problem

Solution

  • You can do it with

    =INDIRECT(ADDRESS(1; MATCH(1;E2:ZZ2)+4))

    The inner MATCH searched for "1" in the given array (from E2 to ZZ2). The "+4" is there, because you are beginning to search in column E (=5) and MATCH returns the column within your array.

    The ADDRESS takes the first row and the column found by MATCH.

    The INDIRECT returns the value of the given ADDRESS.

    If you copy this formula down the rows, the search array (E2:ZZ2) is automatically adjusted to search the corresponding row.