Index-Match find non-blank. Working in Excel 365, but not Excel 2010.

[RockandGrohl]

Hi guys, the below formula works when placed in the rightmost column of the table:

Brochure_Region

OptIn_NH_LandMail

Last_Booked

Last_Booked

FamilyFun

FamilyFun

UK

UK

Sports

Sports

Events

Events

EU

EU

Showtime

Showtime

OptIn_ThirdParty

OptIn_DirectEmail

Results

<>0

<>0

>=0

<=1000000

>=1

<=10000

19503​

FamilyFun

<>0

<>0

>=0

<=1000000

>=1

<=10000

3315​

Sports

<>0

<>0

>=0

<=1000000

>=1

<=10000

8785​

Events

=INDEX($E$1:$P$1,MATCH(TRUE,$E2:$P2<>"",0))

What this does is finds whatever cell is non-blank in the Match range (E2:P2) and then returns the relevant column header.

When I record the macro, it records as Formula2R1C1

However, this is incompatible with Excel 2010. Is there a version of this I can use that will work in both Excel 2010 and 365? Thank you.

 

[Fluff]

That should work in 2010, but it will need array entry (ie Ctrl Shift Enter)

 

[RockandGrohl]

That should work in 2010, but it will need array entry (ie Ctrl Shift Enter)

I've literally just found one that appears to work in both versions:

=INDEX($E$1:$P$1,MATCH(TRUE,INDEX($E3:$P3<>"",0,0),0))

But thanks anyway amigo!

 

[Fluff]

Glad you sorted it & thanks for the feedback.