Using OFFSET in INDEX

**StanUkr** · 02-21-2007, 05:30 AM

Hi,
I have a set of data organized like this:

Column A Column B
Row 1 1 A
Row 2 B
Row 3 C
Row 4 D
Row 5 У
Row 6 2 X
Row 7 Y
Row 8 Z
Row 9 W

and need to return value A1 in column C in rows 1-5 and value A6 in rows 6-9. I suppose to use INDEX and nested OFFSET. How i can formulate OFFSET function in C1:C5 to freeze the array A1:A5 and for C6:C9 to return the array A6:A9? Do anyone have suggestions how to do this? or may be there is another way to reach?
Thanks in advance for any thoughts on this point

**starguy** · 02-21-2007, 06:55 AM

Originally Posted by StanUkr

Hi,
I have a set of data organized like this:

Column A Column B
Row 1 1 A
Row 2 B
Row 3 C
Row 4 D
Row 5 У
Row 6 2 X
Row 7 Y
Row 8 Z
Row 9 W

and need to return value A1 in column C in rows 1-5 and value A6 in rows 6-9. I suppose to use INDEX and nested OFFSET. How i can formulate OFFSET function in C1:C5 to freeze the array A1:A5 and for C6:C9 to return the array A6:A9? Do anyone have suggestions how to do this? or may be there is another way to reach?
Thanks in advance for any thoughts on this point

Hi
you can use seperate formulas (seperate ranges) for both results.

**StanUkr** · 02-21-2007, 07:25 AM

Originally Posted by starguy

Hi
you can use seperate formulas (seperate ranges) for both results.

It'll be too many manual work - too many defferent ranges, their list too long as well

**daddylonglegs** · 02-21-2007, 07:38 AM

Hello Stan

Try this formula in c1 copied down

=LOOKUP(9.99999999999999E+307,A$1:A1)

**StanUkr** · 02-21-2007, 07:51 AM

Originally Posted by daddylonglegs

Hello Stan

Try this formula in c1 copied down

=LOOKUP(9.99999999999999E+307,A$1:A1)

thanks daddy, this realy does work for column A but doesnt for next one
take a look on the attached. Can you explain for me in short how LOOKUP works? thanks again

**daddylonglegs** · 02-21-2007, 08:20 AM

The formula I suggested only works for numbers. If you want it to work for letters and numbers try this formula in D2 copied across and down

=LOOKUP(2,1/(A$2:A2<>""),A$2:A2))

……or perhaps easier, assuming you'll always have an entry in row 2....

=IF(A2="",D1,A2)

**StanUkr** · 02-21-2007, 08:52 AM

Originally Posted by daddylonglegs

The formula I suggested only works for numbers. If you want it to work for letters and numbers try this formula in D2 copied across and down

=LOOKUP(2,1/(A$2:A2<>""),A$2:A2))

……or perhaps easier, assuming you'll always have an entry in row 2....

=IF(A2="",D1,A2)

Thank you for your help - now it works - though i still can't catch its mechanism

. I've used formula II previously, but it needs for manually filled 1st row, that's why I've tried to search better solution. Just for curiosity - is it here can be used kind of moving offset to return arrays with such borders - from filled cell till next filled cell? Do anyone have any suggestions?

Using OFFSET in INDEX

LinkBack

Thread Tools

Rate This Thread

Display

Using OFFSET in INDEX

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions