Forum Discussion

Copper Contributor

Jun 14, 2023

Solved

I want to find out header name on the basis of starting value of each row

Hi,

I want to find out header name on the basis of starting value of each rows.

For Example: as per below screenshot, the start point value in row 2 is from column G then in Column N the formula should retreive 2028 as header name.

Thanks for your help in advance 🙂

excel

Formulas and Functions

Lorenzo
Jun 15, 2023
Hi Yash9154
Alternative with XLOOKUP (2021 & 365)

in N2
=XLOOKUP(TRUE, B2:L2 > 0, B$1:L$1, "No match")

PS: There's a Search button on this site and other forums where the same question has been answered nnn times
Sampke_Yash9154.xlsx10 KB

4 Replies

OliverScheurich
Gold Contributor
Jun 15, 2023
Yash9154
=INDEX($B$1:$L$1,SMALL(IF($B2:$L2>0,COLUMN($B$1:$L$1)-1),1))
An alternative for Excel 2013 could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
Lorenzo
Silver Contributor
Jun 15, 2023
Hi Yash9154
Alternative with XLOOKUP (2021 & 365)

in N2
=XLOOKUP(TRUE, B2:L2 > 0, B$1:L$1, "No match")

PS: There's a Search button on this site and other forums where the same question has been answered nnn times
Sampke_Yash9154.xlsx10 KB
Harun24HR
Silver Contributor
Jun 14, 2023
Yash9154 You can try FILTER() function.

=INDEX(FILTER($B$1:$G$1,B2:G2>0),1,1)

To make it dynamic spill array try-

=BYROW(B2:G5,LAMBDA(x,INDEX(FILTER($B$1:$G$1,x>0),1,1)))
- Yash9154
  Copper Contributor
  Jun 15, 2023
  Thanks for the solution 🙂 it worked for me