Index and Match formula to exclude blanks

[Rchudasama]

Index and match formula but need it to exclude the blanks

Example

1 A B C D E F G
2 Name 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month
3 a 1.9 1.1 1.8
4 b 5.1 1.0 0 2.8

I put in a formula to bring back the month when "a" or "b" reaches >1 for the first time

Formula results
a 1 Month INDEX($C$2:$H$2,MATCH(TRUE,INDEX(C3:H3>1,0),0) - but should be 4 Month
b 1 Month INDEX($C$2:$H$2,MATCH(TRUE,INDEX(C4:H4>1,0),0)) - but should be 2 month

I noticed that this happened because in "a" has blank cells in 1 month, 2 month and 3 month and "b" has blank cells in 1 month

How do I write the formula to exclude the blanks??

Thanks

 

[Jonmo1]

I would suspect that your 'blanks' are not really blank.
Are they formulas that are returning "" ? That is NOT considered blank, it's a text string.

Try
INDEX($C$2:$H$2,MATCH(1,INDEX((C3:H3>1)*(ISNUMBER(C3:H3)),0),0))

 

[Rchudasama]

I would suspect that your 'blanks' are not really blank.
Are they formulas that are returning "" ? That is NOT considered blank, it's a text string.

Try
INDEX($C$2:$H$2,MATCH(1,INDEX((C3:H3>1)*(ISNUMBER(C3:H3)),0),0))

OMG thank you!!!! this works!!! your awesome you don't know how much time you saved me!!

 

[Jonmo1]

You're welcome.

 

[Peter_SSs]

Welcome to the MrExcel board!

FWIW, I think you could also have made this very minor change to your existing formula

=INDEX($C$2:$H$2,MATCH(TRUE,INDEX(C3:H3-1>0,0),0))

 

[Jonmo1]

=INDEX($C$2:$H$2,MATCH(TRUE,INDEX(C3:H3-1>0,0),0))

Nice!

 

[wideboydixon]

I suspect those cells aren't blank but perhaps contain a single space or something that is greater than zero. Try this instead:

=INDEX($C$2:$H$2,MATCH(1,INDEX((C3:H3>1)*(ISNUMBER(C3:H3)),0),0))

WBD

 

[Peter_SSs]

Try this instead:

=INDEX($C$2:$H$2,MATCH(1,INDEX((C3:H3>1)*(ISNUMBER(C3:H3)),0),0))

WBD

This as opposed to post #2?

 

[wideboydixon]

This as opposed to post #2?

I was writing my answer and got called away so it took me longer than I anticipated to post it. I prefer your solution anyway

WBD

 

[Peter_SSs]

I was writing my answer and got called away so it took me longer than I anticipated to post it.

Ah, fair enough. I've been caught like myself sometimes.