Find first and last cells in range, return info beside

M

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

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))
 
M

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top