I have a big table of data that is sort of like a big scatter graph, i.e., whether a column has data in a particular row depends on which row it is. What I need is a formula that finds the correct row based on a name and then finds the first column in that row with data and returns the header value. Actually, I can achieve that with the index match formula like this:
=INDEX(B$1:H$1,MATCH(TRUE,INDEX(B2:H2<>0,),0))
But what I cannot seem to figure out is how to get it to find the next data column in that row. I tried to use the CELL formula to pull the location of the first data cell and then adjust the starting cells for the INDEX MATCH formula, but after about 100 attempts and still no success I decided I'd call on the Chandoo wizards for some help.
Obviously I can't share my workbook due to data privacy concerns, but I've created just a little sample workbook to try to illustrate what I'm trying to accomplish... The summary part at the bottom is the result I'm after, and like I mentioned, I can get the first column of data to return after the names, but it's the ones out to the right that are challenging me.
=INDEX(B$1:H$1,MATCH(TRUE,INDEX(B2:H2<>0,),0))
But what I cannot seem to figure out is how to get it to find the next data column in that row. I tried to use the CELL formula to pull the location of the first data cell and then adjust the starting cells for the INDEX MATCH formula, but after about 100 attempts and still no success I decided I'd call on the Chandoo wizards for some help.
Obviously I can't share my workbook due to data privacy concerns, but I've created just a little sample workbook to try to illustrate what I'm trying to accomplish... The summary part at the bottom is the result I'm after, and like I mentioned, I can get the first column of data to return after the names, but it's the ones out to the right that are challenging me.