Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two Dimensional lookup (Excel 2007)

    I am trying to setup two dimensional lookup formula but i get error in the attached sheet. I request for some help from the gurus!!
    Regards
    Novice
    Attached Files Attached Files

  2. #2
    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: Two Dimensional lookup (Excel 2007)

    If this is not specific to XL2007, dould you post a version that is saved to the earlier XL format for those of us without XL2007.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Dimensional lookup (Excel 2007)

    Hi Steve,
    Attached worksheet is in excel 97-2003 format.
    tks for the prompt response.
    rgds
    novice
    Attached Files Attached Files

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Two Dimensional lookup (Excel 2007)

    I think the formula you want in E16 is:
    <code>=VLOOKUP(D$15,$D$5:$I$9,MATCH(D16,$D$4:$I$4, 0))</code>
    then copy down.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Dimensional lookup (Excel 2007)

    Hi Rory,
    I want the formula to give me the value at intersection of D15 and D16, thereafter D16, D17 and D17,D18 so on from the table.
    novice

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Two Dimensional lookup (Excel 2007)

    Oh, then you want:
    <code>=VLOOKUP(D15,$D$5:$I$9,MATCH(D16,$D$4:$I$4,0 ),FALSE)</code>
    in E16 and copy down.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Dimensional lookup (Excel 2007)

    Hi Rory,
    I tried the formula as suggested by you but it didn't work. One way to solve the problem is to have all the labels in the first row and column in an ascending order, in which case the following Formula works =VLOOKUP(D15,$D$5:$I$9,MATCH(D16,$E$4:$I$4,)+1). However, it would be nice to have a formula which can give the correct data even if the first row and col labels are not in ascending order.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Two Dimensional lookup (Excel 2007)

    we must be talking at cross purposes then as this is what I thought you wanted.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Dimensional lookup (Excel 2007)

    Hi Rory,

    Thanks for the prompt response. I realsise that i was not copying the correct formula. Your worksheet and the formula indeed works and solves my problem.
    Really appreciate your patience with a novice like me
    rgds/novicexcelite

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Two Dimensional lookup (Excel 2007)

    Glad to help. We're all novices at something! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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