Results 1 to 7 of 7

Thread: Looking up (XP)

  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Hillsdale, New Jersey, USA
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking up (XP)

    How can I do a lookup when the reference is not in either the same row or column? In the attached example, I want to return the value in B2 (4567) by looking up the value in A1 (12345).

    Thanks!

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

    Re: Looking up (XP)

    What are the lookup criteria? Since only A1 and B2 are populated, it is not clear what you want. Could you provide a more complete description?

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Location
    Hillsdale, New Jersey, USA
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking up (XP)

    If A2 were popluated with 4567, I know I could hlookup(A2,$A:$A,1,false) to return the value in A2. If B1 were populated with 4567, I know I could vlookup(A1,$A:$B,2,false) to return the value in B1.

    However, I'm trying to do a lookup based on a reference that isn't in the same row or column of the reference. What I should have mentioned, is that the result is always 1 row down and 1 column across for the referenced cell. Hope that makes more sense now.

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

    Re: Looking up (XP)

    Let's say that you enter the lookup value in cell E1. Enter the following formula in E2 (or where you need it):
    <code>
    =OFFSET($A$1,MATCH(E1,$A:$A,0),1)
    </code>
    MATCH(E1,$A:$A,0) looks for an exact match of the value of E1 in column A, returning the index (row number) of the match, or #N/A if there is no match.
    The OFFSET function starts at cell A1, moves the number of rows returned by MATCH down, and one column to the right, and returns the value of that cell.

    See attached version

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Location
    Hillsdale, New Jersey, USA
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking up (XP)

    Got it! I thouht that I had to use offset() and match() somehow but after hours of trying to "get it" myself, your reply is really appreciated.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Looking up (XP)

    Hi there

    without further information I am assuming you are looking for a value, say 12345 and then wanting to offset by 1 column and then 1 row. Try putting this formula in a cell (say F2 in your example and place 12345 in E2):

    =OFFSET($A$1,MATCH(E2,$A$1:$A$4,0),1,1)

    Is that what you are looking for?
    Jerry

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Looking up (XP)

    oops that will teach me to go off line and then sort a question out...make <img src=/S/note.gif border=0 alt=note width=20 height=20> to myself, check before posting <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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
  •