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.

**BUT**

It may be easier to use the `offset`

formula in this case.

```
=Offset(Sheet1!A1,0,5)
```

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:

```
=Iferror(Offset(Sheet1!A1,0,MATCH(B1,Sheet1!$AY$2:$AY$50000,0)-1),"")
```