Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Find Values in Another Worksheet

    I have a pivot table with this format:
    HTML Code:
    Key       Date1    Date2...
    1250       1.25      1.20
                 1.30       1.4
                  1.50       1.6
    From another worksheet, I want to find the key "1250" and the 3rd item down the list. What I need is a cell formula to find "1.50".

    Thanks.
    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, Jerry. Would not an OFFSET work? Perhaps if you could post a sample of what you have and what you desire.

    (PS I am traveling the next 11 days so can't respond any further. Hopefully someone can take it from here)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    This is the solution I worked out to search for a value in another worksheet and look up a value in a cell that is offset from the search target.
    =OFFSET(Pivot_Sheet!$A$1,MATCH($L$4, Pivot_Sheet!$A:$A,0)+2,2)

    If $L$4 contains the client identification number "1251", the MATCH portion of this formula finds 1251 in Pivot_Sheet, column A, and the MATCH function evaluates to the row number of the target. Since I want the 3rd item in the list for each client, I add +2 to the row number from MATCH.
    The first data column in a series of columns is 2 columns to the right of the target. The "+2,2" portion of the OFFSET returns the value that is 2 columns down from the search target and 2 columns to the right. Resolved!

    Thanks for your reply.
    Jerry

Posting Permissions

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