using offset function to create dynamic range based on a match search

**freddiethomas** · 02-08-2013, 03:06 AM

Good morning,

I need a formula to define a dynamic range that varies based on a search criteria & then an offset. It's a bit of a complex problem to explain in words so I have tried to explain it in a little spreadsheet attached!

help.xlsx

Thanks very much in advance,

**JBeaucaire** · 02-08-2013, 03:30 AM

Maybe:

=OFFSET($F$6,,$B$6,,)

**freddiethomas** · 02-08-2013, 03:41 AM

Thanks JB,

I have just got it working with

=OFFSET(INDEX(somerange,1,MATCH(searchrange,searchlocation,0)),-3,0,1,1)

**JBeaucaire** · 02-08-2013, 04:13 AM

There you go.

I just spotted your duplicate post... don't do that. Read the forum rules (link above), you're expected to know and abide them. ok? Thanks.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

**twiggywales** · 02-08-2013, 05:34 AM

belive it of not you dont need to use offset to make a dynamic list

you can use an index eitherside (or both sides) of a : symbol. this works as index gives a cell reference not a value.

(for example,
=INDEX(A1:C3,2,1) actully gives you a cell reference to cell A2, this is then shown as a value because of the = symbol)