Find first and last cells in range, return info beside

[mjones]

Hi All,

I hope someone can help with a tricky formula. Given this type of
sample data:

A B C D E
F G
1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10
2 x
x x
3 x x x x
4 x x
5
x x x
6
7

Looking at row 2 -
In cell A6, find the first (left most) x and return the month-year in
the row above it. This should give Apr-10.
In cell A7, find the last (right most) x and return the month-year in
the row above it. This should give Jun-10.
Then I should be able to determine the formula for rows 3 to 5.

If someone knows this, it will save a lot of people manual entries.

Thanks!

Michele

 

[T. Valko]

Try these...

For the leftmost "x":

=INDEX(A1:G1,MATCH("x",A2:G2,0))

For the rightmost "x":

=INDEX(A1:G1,MATCH("xxxxx",A2:G2))

 

[mjones]

Try these...

For the leftmost "x":

=INDEX(A1:G1,MATCH("x",A2:G2,0))

For the rightmost "x":

=INDEX(A1:G1,MATCH("xxxxx",A2:G2))

Thank you very much. Your formulas work great on a test worksheet.
Now I just have to determine why they are giving me a #NA - value not
available to the formula or function. There are month/years in all
the appropriate rows. I'm sure I'll work it out. Thanks again.

 

[T. Valko]

There are month/years in all the appropriate rows

It looks like x's in the data rows and the date (to be returned) is the in
the header row.

Both formulas are specifically looking for x's in the data rows.

--
Biff
Microsoft Excel MVP

Try these...

For the leftmost "x":

=INDEX(A1:G1,MATCH("x",A2:G2,0))

For the rightmost "x":

=INDEX(A1:G1,MATCH("xxxxx",A2:G2))

Thank you very much. Your formulas work great on a test worksheet.
Now I just have to determine why they are giving me a #NA - value not
available to the formula or function. There are month/years in all
the appropriate rows. I'm sure I'll work it out. Thanks again.