Help with INDEX function and dragging formulas

[Archadius]

Hi MrExcel community,

I'm new to the forum and also not an experienced user of Excel - trying to get my way around it though.

I have the following problem to be solved, tried some research with no success. This is the simplification of the actual issue I have but if I'm able to do what I will explain below, I can solve my real issue. Please refer to the table.

Column A is formed by a combination of elements from column D and a fixed value Z (which in the example is located in F4).

In order to create column A, what I have been trying to do is to look elements up using INDEX, so that A1 receives =INDEX(D$1:D$7, 1), A2 receives F$4 and subsequently, A3 =INDEX(D$1:D$7, 2), A4 = F$4...

If I try to drag the formula down using 6 cells with that logic it doesn't work - I was expecting the second argument inside INDEX to increment but it doesn't. Dragging these cells down just repeat them.

What can be done to solve this?

Thanks in advance,

	A	B	C	D	E	F
1	A			A
2	Z			B
3	B			C
4	Z			D		Z
5	C			E
6	Z			F
7				G
8				H

<tbody>
</tbody>

 

[steve the fish]

Hi. Try:

=IFERROR(IF(MOD(ROWS($A$1:A1),2),INDEX($D$1:$D$8,ROUNDUP(ROWS($A$1:A1)/2,0)),$F$4),"")