SOLVED

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

Copper Contributor

# 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))

3 Replies
best response confirmed by PolyPeter (Copper Contributor)
Solution

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

``=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.

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

it works.... ! thank you so much, Please share the formula for the first pay.... @OliverScheurich

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

``=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.

1 best response

Accepted Solutions
best response confirmed by PolyPeter (Copper Contributor)
Solution

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

``=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.