Having trouble with a lookup when searching fo rhte first nonblank cell as a
reference point.

A B C D E F G
1 24 24 24
2 18 12 12
3 8
4 06/15 06/22 06/29 07/06 07/13 07/20

In cell A1 I need a formula that will return 07/06, A2 returns 06/22, and A3
returning 07/13.

Hi Jes,

Array enter the following formula:

=OFFSET($A$4,0,MATCH(TRUE,ISBLANK(B3:G3),0))

and copy it down. By array enter we mean press Shift Ctrl Enter instead of
Enter to enter the formula.
--

Cheers,
Shane Devenshire


"JES" wrote:

Having trouble with a lookup when searching fo rhte first nonblank cell as a
reference point.

A B C D E F G
1 24 24 24
2 18 12 12
3 8
4 06/15 06/22 06/29 07/06 07/13 07/20

In cell A1 I need a formula that will return 07/06, A2 returns 06/22, and A3
returning 07/13.

Try this:

=INDEX(B$4:G$4,MATCH(TRUE,INDEX(B1:G1<"",,0),0))

Copy down as needed

--
Biff
Microsoft Excel MVP


"JES" wrote in message
...
Having trouble with a lookup when searching fo rhte first nonblank cell as
a
reference point.

A B C D E F G
1 24 24 24
2 18 12 12
3 8
4 06/15 06/22 06/29 07/06 07/13 07/20

In cell A1 I need a formula that will return 07/06, A2 returns 06/22, and
A3
returning 07/13.

Nice ! ... and to save a few keystrokes:

=INDEX(B$4:G$4,MATCH(TRUE,INDEX(B1:G1<"",),))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
Try this:

=INDEX(B$4:G$4,MATCH(TRUE,INDEX(B1:G1<"",,0),0))

Copy down as needed

--
Biff
Microsoft Excel MVP


"JES" wrote in message
...
Having trouble with a lookup when searching fo rhte first nonblank cell
as a
reference point.

A B C D E F G
1 24 24 24
2 18 12 12
3 8
4 06/15 06/22 06/29 07/06 07/13 07/20

In cell A1 I need a formula that will return 07/06, A2 returns 06/22, and
A3
returning 07/13.