Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Statford, Prince Edward Island, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using offset and match (Excel 2002)

    I am trying to find the value in one table of data based upon matched values in another table. This seems a good use for offset and match but I can't get the formula to work, I keep getting an error with the formula I have created: OFFSET(MATCH(F1,A1:A12,0),1,1). The worksheet I am trying to use it in is attached. This is a demo sheet. Just tell me what stupid mistake I am making. THanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: using offset and match (Excel 2002)

    The workbook you attached doesn't tell us what you want to accomplish. Can you explain in words what you want to look up? Perhaps an example would make it clearer.

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Location
    Statford, Prince Edward Island, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using offset and match (Excel 2002)

    I want to show the value in column B that is one row lower than the value in column A that matches the value in row E1:K1. e.g. find the value "ab" in col. A and then return the result "300" which is the value one row lower in column B. I will need to be able to modify the offset so I can find the value one row higher in another column. thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: using offset and match (Excel 2002)

    Are you looking for in E3:
    <pre>=OFFSET(INDIRECT("A"&E2),1,1)</pre>


    or perhaps without using the intermediate formula:
    <pre>=OFFSET(INDIRECT("A"&MATCH(F1,$A$1:$A$12,0)), 1,1)</pre>


    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: using offset and match (Excel 2002)

    Try a combination of INDEX (instead of OFFSET) and MATCH:
    <code>
    =INDEX($A$1:$B$13,MATCH(F1,$A$1:$A$13,0)+1,2)
    </code>
    will look up the value in column B one row down from the row where the value from F1 is found.

  6. #6
    New Lounger
    Join Date
    Feb 2001
    Location
    Statford, Prince Edward Island, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using offset and match (Excel 2002)

    that works, thank you

  7. #7
    New Lounger
    Join Date
    Feb 2001
    Location
    Statford, Prince Edward Island, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using offset and match (Excel 2002)

    that also works, thank you

Posting Permissions

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