Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Want to break it (2000 SR2)

    I have a chart of values that uses Match and Choose to find values. E15 is the formula that I would like to change to a dynamic vlookup based on the value in C15 which dictates the column for the lookup. I have looked high and low and haven't come up with anything yet.

    Anyone got ideas that would allow me to do this without VBA?
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Want to break it (2000 SR2)

    How about:

    =INDEX(B2:M11,MATCH(C15,OFFSET(B2,0,MATCH(D15,C2:M 2,1),20,1),1),MATCH(D15,C2:M2,1))

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Want to break it (2000 SR2)

    cpod,

    Nope. If I change the value in D15 to 50 then it returns 80 instead of 1/2". Haven't had time to analyze your formula but it looks like it might be in the right direction and less complicated than what I am using.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Want to break it (2000 SR2)

    Thom,

    Maybe this will help.

    =INDIRECT("B" & MATCH(C15,OFFSET(B2,0,MATCH(D15,C2:M2,1),10,1),1)+ 1)

    Ken

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Want to break it (2000 SR2)

    Thanks you all. I went with a modified formula that cpod suggested. I also decided that I needed to lookup an equal or larger value. The attached spreadsheet will do just what I was after!!
    Attached Files Attached Files

Posting Permissions

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