Table_array in VLOOKUP function, how to be dynamic moving between series of data?

[Murad_]

Hi,

My question, how can I make the Table_array in VLOOKUP function dynamic?
Example:

for this formula in Cell A1
=VLOOKUP($E$2,Sheet1!$C$2:$M$7,2,FALSE)
Cell A2
=VLOOKUP($E$2,Sheet1!$C$8:$M$13,2,FALSE)
Cell A3
=VLOOKUP($E$2,Sheet1!$C$14:$M$19,2,FALSE)

what is the formula that I can use to replace the numbers 8 and 13 in Cell A2, and the number 14 and 19 in Cell A3??

 

[James006]

Hi,
You can test following in row 1 ... and copy down for incrementing

=MOD(ROW(),5)+5*(ROW()-1)+1

 

[James006]

This will result into

=VLOOKUP($E$2,INDIRECT("Sheet1!C"&(MOD(ROW(),5)+5*(ROW()-1)+1)&":M"&(MOD(ROW(),5)+5*(ROW()-1)+6)),2,FALSE)

 

[Murad_]

Thank you for your reply, I tried but I got this #N/A, how may I fix it?

 

[James006]

Sorry ...

But I have stupidly followed what you wrote :

Formula in Cell A1
=VLOOKUP($E$2,Sheet1!$C$2:$M$7,2,FALSE)
Cell A2
=VLOOKUP($E$2,Sheet1!$C$8:$M$13,2,FALSE)
Cell A3
=VLOOKUP($E$2,Sheet1!$C$14:$M$19,2,FALSE)