I am looking for a formula that will find the value in cell B1 in the row B5:I5. Then go down from that date to the first non-blank cell. Then go left and return the corresponding value/name in column A6:A12 into cell F1.
Thanks in advance. I have been racking my head trying to figure this out. I was using =INDEX($A$6:$A$12,SMALL(IF($H$6:$H$12<>"",ROW($H$6:$H$12)-ROW($H$6)+1),ROW(A1))) to find the nth non-blank cell and return the corresponding name, but that requires the column to be called out in the IF formula. For this I tried to use =MATCH(B1,B5:I5,0). This just gives me the column number within the range. I can't figure out how to combine the two formulas (if that is even possible).
Thanks in advance. I have been racking my head trying to figure this out. I was using =INDEX($A$6:$A$12,SMALL(IF($H$6:$H$12<>"",ROW($H$6:$H$12)-ROW($H$6)+1),ROW(A1))) to find the nth non-blank cell and return the corresponding name, but that requires the column to be called out in the IF formula. For this I tried to use =MATCH(B1,B5:I5,0). This just gives me the column number within the range. I can't figure out how to combine the two formulas (if that is even possible).