Index match to return data from another row

Hi guys

I have changed my spreadsheet to consist of dummy data

Basically I am trying to setup an index match to return data in cell J3 to display the text from Columns C:H, and have a text of 0 for row C3....

I.e. Prominent on website (column C, cell c3 IS 0, so this should be returned in J3. Ideally if there is another 0 (in cells C3 onwards), I want this to be shown too with the corresponding column header so it shows multiple column header titles). Unsure if an INDEX MATCH is the right way to go!

I am also trying this solution: =INDEX(C1:H1,C3:H3,MATCH('BACKGROUND DATA'!E1,0))

Please note the BACKGROUND DATA TAB refers to a cell that says '0'. That is all

Can anyone helps?

Thank you!

Please try at J3
=IFNA(INDEX(C1:H1,MATCH(0,C3:H3,0)),"")&IFERROR(", "&INDEX($A$1:$H$1,AGGREGATE(15,6,COLUMNS(C3:H3)/(C3:H3=0),2)),"")

add this if you need the 3rd Topic
&IFERROR(", "&INDEX($A$1:$H$1,AGGREGATE(15,6,COLUMNS(C3:H3)/(C3:H3=0),3)),"")

Awesome. Looks good, thank you Bo_Ry!

@Bo_Ry/others

unfortunately the formula is returning Contract Type (column F) in all the data, even though it does not have a ZERO. Why is this please? I am trying to change the formula but can't find th eissue. Pleas ehelp

Maybe attach the version you are having trouble with?

Hi Ali

This has been attached now. It was the version of Bo_Ry which he kindly provided to me

Sorry, Please try again

=IFNA(INDEX($C$1:$H$1,MATCH(0,C3:H3,0)),"")&IFERROR(", "&INDEX($A$1:$H$1,AGGREGATE(15,6,COLUMN(C3:H3)/(C3:H3=0),2)),"")

See if this works:

=IFNA(INDEX(C1:H1,MATCH(0,C3:H3,0)),"")&IFERROR(", "&INDEX($B$1:$H$1,,AGGREGATE(15,6,COLUMNS(C3:H3)/(C3:H3=0),2)),"")

Hi Bo_Ry and if there are more than 2 cells with 0, how do I get these to also be returned? it seems to return 2/3 cells only

Hi Ali,

Thanks - same solution as Bo_Ry, but I can't capture multiple cells with 0? Only 2 cells are being returned..

=IFNA(INDEX($C$1:$H$1,MATCH(0,C3:H3,0)),"")&IFERROR(", "&INDEX($A$1:$H$1,AGGREGATE(15,6,COLUMN(C3:H3)/(C3:H3=0),2)),"")
&IFERROR(", "&INDEX($A$1:$H$1,AGGREGATE(15,6,COLUMN(C3:H3)/(C3:H3=0),3)),"")
&IFERROR(", "&INDEX($A$1:$H$1,AGGREGATE(15,6,COLUMN(C3:H3)/(C3:H3=0),4)),"")

add more &IFERROR( and change number in red

this seems good. thanks again!