Combining offset, address, and match

**rhart00** · 04-26-2010, 06:34 PM

Hello All,
I am having issues combining two formulas:

=ADDRESS(5,MATCH("Employee: ",5:5))
=OFFSET($AA$5,0,2)

The output of the address/match formula is $AA$5. I would like to replace the $AA$5 in the offset formula with the address/match formula like so:

=OFFSET(ADDRESS(5,MATCH("Employee: ",5:5)),0,2)

When I do this, excel says that the formula I typed contains an error. I have tried a number of different things with no luck. Any suggestions?

Thanks in advance

**daddylonglegs** · 04-26-2010, 06:41 PM

OFFSET needs a cell reference but the address formula returns a text string, so your formula doesn't work. One way round that would be to convert the ADDRESS function to a reference by using INDIRECT.......but it's much easier to avoid ADDRESS in the first place, try just

=INDEX(5:5,MATCH("Employee: ",5:5)+2)

Note: if the cell you are looking for in row 5 will contain exactly "Employee: " then you probably should use MATCH with a final argument of 0, i.e.

=INDEX(5:5,MATCH("Employee: ",5:5,0)+2)