Return value from another column

**Goofy** · 02-16-2010, 11:10 AM

In column A there are text in row 1, 3, 7 and 15. The rest is blank. I need a fomula in column B that returns the value of A1 into B1 and B2. The value of A3 should be returned into B3, B4, B5 and B6. The value of A7 should be returned into B7, B8, B9, B10, B11, B12, B13 and B14. Does anyone know how to do this?

**zbor** · 02-16-2010, 11:25 AM

I have to go.. Don't time to finnish...

Here is start.. Your data is in rows =POWER(2;ROW(A1)-1)-1

And you need to use them in index function...

Hope someone else can continue

**sailepaty** · 02-16-2010, 02:07 PM

If values in column A are numbers.

[B1]=INDEX($A$1:$A$15,MATCH(9.99E+307,$A$1:A1))

If they are text

[B1]=INDEX(A$1:A$15,MATCH(REPT("Z",255),A$1:A1))

Regards

**zbor** · 02-16-2010, 03:11 PM

Heere, try this (with one aditional helper column)...

**zbor** · 02-17-2010, 02:49 AM

I'm so stupid, grrrrr...

This is solution without helper column:

=INDEX($A$1:$A$1000,LOOKUP(ROWS($A$1:A1),{1,3,7,15,31}))

Difference is that you define values in lookup array because they would so quicly exceed max number of rows that it's useless to create helper colum...

Also, extend range that would fit your data.