Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find value based on column/row intersect (Lookup?) (2002)

    I have a table of values and would like to report on the intersection of the column and row. Please see attached workbook. For example, if Mary's score is 23 (2 is the column label, 3 is the row label) her value is 150. And if Joe's score is 54, his value is 265. I read the first digit as the column and the second digit as the row. What kind of formula will I need? Some type of lookup? Or a nested if?? I know the wizards on this board will have a speedy reply. Thanks in advance!

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Find value based on column/row intersect (Lookup?) (2002)

    Hi Sarah,

    Try: =OFFSET($A$1,LEFT(N2,1),RIGHT(N2,1))
    in cell O2, and copy down as far as needed.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find value based on column/row intersect (Lookup?) (2002)

    Thanks Macropod! That works. Now I'll do a little research to figure out how it works. Pretty cool!

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find value based on column/row intersect (Lookup?) (2002)

    =INDEX(B2:J9,MATCH(--LEFT(N2,1),A2:A9,0),MATCH(--RIGHT(N2,1),B1:J1,0))

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find value based on column/row intersect (Lookup?) (2002)

    And thank you, maxflia10. Do either of the formulas perform more efficiently than the other? Or is it simply another way to accomplish the goal?

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find value based on column/row intersect (Lookup?) (2002)

    I don't know if one is more efficient than the other. OFFSET is a volatile function, whereas Index/Match is not. What you can do to eliminate a couple of function calls is to enter the column/row numbers in separate cells. If the matrix is not huge, there shouldn't be a noticeable difference one way or another. I'd go for the shorter OFFSET, since I'm lazy.

Posting Permissions

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