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

1. 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. Re: USING A INDEX,MATCH,OFFSET TO FIND VALUE (EXCEL2000)

heres a little ex worksheet

3. 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

4. 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
•