Based off of what you're asking you can use the
indirect formula to indirectly reference a cell via it's text name
=Indirect("Sheet1!" & "A1")
That will return the text from sheet 1's A1 cell.
You can implement this inside of your larger formula.
It may be easier to use the
offset formula in this case.
That formula will start at cell A1, and move it 5 cells to the right... so combined with your match formula you could do this..
=IF(MATCH(B1,Sheet1!$AY$2:$AY$50000,0) > 0,Offset(Sheet1!A1,0,MATCH(B1,Sheet1!$AY$2:$AY$50000,0)-1),"")
Both of these approaches are valid ways to use a formula or text to arrive at a new cell.
however, I am unclear which approach best suits your needs due to the vagueness within your original post.
Note that if you have excel 2007+ you can simplify the offset as: