+ Reply to Thread
Results 1 to 3 of 3

Combining offset, address, and match

  1. #1
    Registered User
    Join Date
    04-20-2010
    Location
    west coast
    MS-Off Ver
    Excel 2007
    Posts
    5

    Combining offset, address, and match

    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

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: formula issues combing offset, address, and match

    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)
    Last edited by daddylonglegs; 04-26-2010 at 06:43 PM.

  3. #3
    Registered User
    Join Date
    04-20-2010
    Location
    west coast
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Combining offset, address, and match

    That worked perfect. Thanks daddylonglegs

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1