In A7, I would like to return the number at the beginning of the cell in which I find a certain piece of text. So, for example, if I search for the word "question" I would like to return the number 4.
I have tried using this:
=LEFT(CELL("address",INDEX(A1:A5,MATCH(""&"question"&"",A1:A5,0))),1)
Because CELL("address",INDEX(A1:A5,MATCH(""&"question"&"",A1:A5,0))) returns the cell reference ($A$4), but it takes the cell reference as the text rather than the text in that cell.
Can anyone help?
>Solution :
This formula is not perfect because it cannot handle if there is more than one match or if the number to extract is over 9 but could be a starting point:
Formula in B9:
=LEFT(INDEX($A$1:$A$5,MATCH("*"&B8&"*",$A$1:$A$5,0)),1)