Calculate cell reference

[Confused]

I have the following lookup which is working exactly the way I want:

=VLOOKUP($C$4,Sheet2!$C$2:$M$19,MATCH("1M",Sheet2!$C$1:$M$1,))

The problem is, I need it to change it's lookup based on user input. If the
user types 2 in a certain cell I need this lookup to change to:

=VLOOKUP($C$4,Sheet2!$C$20:$M$37,MATCH("1M",Sheet2!$C$1:$M$1,))

If the user types 3, it needs to change to:

=VLOOKUP($C$4,Sheet2!$C$38:$M$55,MATCH("1M",Sheet2!$C$1:$M$1,))

I can calculate the cell numbers easily enough:

=2+B10*18-18

Which will give me the cell I need to reference, B10 being the cell the user
types 1,2,3, etc into, the result if 2 is entered being 20, if 3 is entered,
it results in 38, etc.

The problem is, I can't figure out how to tell the lookup that the range it
needs to look at has to change. I've tried entering the formula or a
reference to the cell that calculates the change, but that doesn't work.

Any ideas how this might be done?

Thanks
Conf.

 

[Pete_UK]

You can use INDIRECT, like this:

=VLOOKUP($C$4,INDIRECT("Sheet2!C"&(B10*18-16)&":M"&(B10*18+1)),MATCH
("1M",Sheet2!$C$1:$M$1,))

Hope this helps.

Pete

 

[Confused]

Aha! Excellent, exactly what I needed, thank you very much.

Conf.

 

[Pete_UK]

You're welcome - hopefully you're not so confused now. <bg>

Pete

 

[Confused]

At least not in that area, but there's always something else to be confused
about, lol

Thanks again.