Vlookup Range Issues

[Salesopspain]

I'm working with a large data set and need to click & drag my vlookup formula but need the range to auto adjust. I have an example of my current structure below where I manually shift the range.

Cell B2: =ifna(vlookup($D$1,'Sheet 2'!$A$1:I$685,7,0),0)
Cell C2: =ifna(vlookup($D$1,'Sheet 2'!$K$1:S$685,7,0),0)
So on and so forth for 365 cells

How can I setup the formulas so the range automatically adjusts 10 cells over?

 

[dave3009]

Hi and welcome to the board.

Q1. What is below row 685?
Q2. Have you considered using a table?

 

[Salesopspain]

Hi and welcome to the board.

Q1. What is below row 685?
Q2. Have you considered using a table?

Q1: Nothing is below it
Q2: I have not but I didn't think the data on Sheet 2 would fit well into one table. Each range of cells in the vlookup makes up 1 day; with 365 different ranges each with a similar data set I didn't think a table would work well.

 

[dave3009]

OK thanks, give this a try

Cell B2: =ifna(vlookup($D$1,'Sheet 2'!$A:$I,7,0),0)
Cell C2: =ifna(vlookup($D$1,'Sheet 2'!$K:$S,7,0),0)

Or

B2: =XLOOKUP($D$1,'Sheet 2'!$A:$A,'Sheet 2'!$G:$G,0)
C2: =XLOOKUP($D$1,'Sheet 2'!$K:$K,'Sheet 2'!$Q:$Q,0)

 

[Salesopspain]

OK thanks, give this a try

Cell B2: =ifna(vlookup($D$1,'Sheet 2'!$A:$I,7,0),0)
Cell C2: =ifna(vlookup($D$1,'Sheet 2'!$K:$S,7,0),0)

Or

B2: =XLOOKUP($D$1,'Sheet 2'!$A:$A,'Sheet 2'!$G:$G,0)
C2: =XLOOKUP($D$1,'Sheet 2'!$K:$K,'Sheet 2'!$Q:$Q,0)

That's not exactly solving my current problem - I'm trying to have the column parameters change without me manually inputing the $K:$S portion. i.e. if I wanted to go to D2 the formula would automatically update to =ifna(vlookup($D$1,'Sheet 2'!$U:$AC,7,0),0)

Does that make more sense? Sorry for the poor initial description

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1				xx
2		23	46	0	0
3		23	46	0	0	0
Sheet13

Cell Formulas
Range	Formula
B2:E2	C2	=IFNA(VLOOKUP($D$1,INDEX(Sheet2!$1:$1,COLUMNS($B2:C2)*10-9):INDEX(Sheet2!$685:$685,COLUMNS($B2:C2)*10-1),7,0),0)
B3:NB3	B3	=SUMIFS(OFFSET(Sheet2!G1:G685,0,SEQUENCE(,365,0,10)),OFFSET(Sheet2!A1:A685,0,SEQUENCE(,365,0,10)),D1)
Dynamic array formulas.

The B2 formula you'll have to drag across 365 times. The B3 formula you just need to enter in B3 once, but it only works if the data you want to return is numeric.

 

[Salesopspain]

Try:

Book1
	A	B	C	D	E	F
1				xx
2		23	46	0	0
3		23	46	0	0	0
Sheet13

Cell Formulas
Range	Formula
B2:E2	C2	=IFNA(VLOOKUP($D$1,INDEX(Sheet2!$1:$1,COLUMNS($B2:C2)*10-9):INDEX(Sheet2!$685:$685,COLUMNS($B2:C2)*10-1),7,0),0)
B3:NB3	B3	=SUMIFS(OFFSET(Sheet2!G1:G685,0,SEQUENCE(,365,0,10)),OFFSET(Sheet2!A1:A685,0,SEQUENCE(,365,0,10)),D1)
Dynamic array formulas.

The B2 formula you'll have to drag across 365 times. The B3 formula you just need to enter in B3 once, but it only works if the data you want to return is numeric.

Thanks! That got things up and running

 

[Eric W]

Glad we could help!