Lookup based on 1st non-blank cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rominall - 14 Sep 2007 16:22 GMT

This should be easy but I'm having problems. Data looks like this.

Jan Feb Mar Apr.........Dec
John 10 5 6
Mary 2 4
Jill 5 1 5

I want to add a column that will find the first non-blank cell after the
name and then return the column header to that cell so it looks somethng like
this.

Name Start
John Feb
Mary Mar
Jill Jan

And if there is never a non-blank cell I'd like it to return N/A.

Reply to this Message

T. Valko - 14 Sep 2007 18:05 GMT

Try this:

=INDEX(B$1:M$1,MATCH(TRUE,INDEX(B2:M2<>"",1,),0))

Copy down as needed.

Signature

Biff
Microsoft Excel MVP

> This should be easy but I'm having problems. Data looks like this.
>
[quoted text clipped - 14 lines]
>
> And if there is never a non-blank cell I'd like it to return N/A.

Reply to this Message

Dave Peterson - 14 Sep 2007 18:12 GMT

=IF(COUNTA($B2:$M2)=0,"N/A",INDEX($B$1:$M$1,MATCH(TRUE,$B2:$M2<>"",0)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

or if you can live with #N/A:
=INDEX($B$1:$M$1,MATCH(TRUE,$B2:$M2<>"",0))

> This should be easy but I'm having problems. Data looks like this.
>
[quoted text clipped - 13 lines]
>
> And if there is never a non-blank cell I'd like it to return N/A.

Signature

Dave Peterson

Reply to this Message

Teethless mama - 14 Sep 2007 18:30 GMT

Assuming
Months on cells B1:M1
Name on cells A2:A4

Create defined names for all the names
eg. John is a defined name range from B2:M2
Mary is a defined name range from B3:M3 and so on...

If your criteria names
eg. Mary in cell A9
John in cell A10 and so on...

In cell B9: =INDEX($B$1:$M$1,MATCH(TRUE,INDIRECT(A9)<>"",0))
ctrl+shift+enter, not just enter
copy down

> This should be easy but I'm having problems. Data looks like this.
>
[quoted text clipped - 13 lines]
>
> And if there is never a non-blank cell I'd like it to return N/A.

Reply to this Message