Forum Discussion

PolyPeter's avatar
PolyPeter
Copper Contributor
Jan 23, 2023

Formula to find first and last value on row and return corresponding header column

In my spreadsheet i have row L19:AZ19 which contain duplicate number data and empty cell where i want function to find the last occurrence number then return column Header $L$2:$AZ$2 which contain Months I have try CHATGBT to find the formula but it doesn't give me the correct ones, it give me this but it only correct when the last number is unique (not duplicate) but if the last number is duplicate it return the first occurrence which it find. =INDEX($L$2:$AZ$2,1,MATCH(LOOKUP(2,1/(L19:AZ19<>""),L19:AZ19),L19:AZ19,0))

 

 

  • PolyPeter 

    =INDEX($L$2:$AZ$2,LARGE(IF((ISNUMBER($L19:$AZ19))*($L19:$AZ19<>""),COLUMN($L$1:$AZ$1)-11),1))

    You can try this formula to find the last value and return the corresponding header column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

  • PolyPeter 

    =INDEX($L$2:$AZ$2,LARGE(IF((ISNUMBER($L19:$AZ19))*($L19:$AZ19<>""),COLUMN($L$1:$AZ$1)-11),1))

    You can try this formula to find the last value and return the corresponding header column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        PolyPeter 

        =INDEX($L$2:$AZ$2,SMALL(IF((ISNUMBER($L19:$AZ19))*($L19:$AZ19<>""),COLUMN($L$1:$AZ$1)-11),1))

        You are welcome. You can try this formula to find the first value and return the corresponding header column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

         

         

Resources