Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    USING A INDEX,MATCH,OFFSET TO FIND VALUE (EXCEL2000)

    HELLO,

    i AM CURRENTLY TRYING TO LOOK FOR A VALUE. I have two worksheets. the work sheet where the value the formula will be is in a sheet called "Pricing" and the cell the formula is in b123 on Pricing!

    so what I have come up with is this
    an array where I am trying to match the text DWW-101 the array is in the worksheet called "table" in the cells U8:AN8
    so once I find the match I have a point of reference. that is where the Index comes in.

    =INDEX('Chem-ProppantSchedule'!AA8:AN8,8,MATCH("DWP-101",'Chem-ProppantSchedule'!U8:AN8,-1))

    Now I would like to offset from where the match is down 57 cells and leave the value found in the formula cell.
    some how I need to add in the offset function. but mu formula above has a kink in it aswell.

    Thanks

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USING A INDEX,MATCH,OFFSET TO FIND VALUE (EXCEL2000)

    heres a little ex worksheet

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

    Re: USING A INDEX,MATCH,OFFSET TO FIND VALUE (EXCEL2000)

    Hi Patrick,

    You could use:
    =INDEX(OFFSET(Table!$U$8:$AN$8,57,),MATCH("DWP-101",Table!$U$8:$AN$8,-1))
    in cell B126.

    I note, though that "DWP-101" doesn't appear in your 'table' sheet, but your formula is adjacent to a cell titled in columns C "Total DWW". If you want the value corresponding to the value in column C, you could use:
    =INDEX(OFFSET(Table!$U$8:$AN$8,57,),MATCH(RIGHT($C 126,LEN($C126)-FIND(" ",$C126)),Table!$U$8:$AN$8,0))
    and copy down for as many rows as needed.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: USING A INDEX,MATCH,OFFSET TO FIND VALUE (EXCEL2000)

    =INDEX('Chem-ProppantSchedule'!AA8:AN8,8,MATCH("DWP-101",'Chem-ProppantSchedule'!U8:AN8,-1))

    I haven't tested this but:
    1. I think you have too many arguments for the Index function. try removing the "8"
    2. Match will give you the item number in the list. You want to offset this by 57, so"
    MATCH("DWP-101",'Chem-ProppantSchedule'!U8:AN8,-1)+57
    should give you the offset
    3. Then do the Index of you other range to get the value of the offset cell:

    =INDEX('Chem-ProppantSchedule'!AA8:AN8,MATCH("DWP-101",'Chem-ProppantSchedule'!U8:AN8,-1)+57)

Posting Permissions

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