General question about arrays

[Marty3280]

Hi. I am a relative newcomer to using combination formulas and arrays so please bear with me. I want to retrieve the first value, a number, in a column of 10 rows that contains blanks. The blanks can be in various cells. I used index(array,match(false,isblank([same] array),0) to get the first nonblank value in the column. It worked when the first row in the array was populated and didn't work if it was not. So I have a couple of questions. Do all formulas containing arrays have to have the first cell in the array populated? If so, how do I work around that? Are there other functions I should be using? I have tried some others like true,isnumber instead of false,isblank with the same result hence the general question.

Thanks for your help,
Marty

[Ace_XL]

Try..

=INDEX(D1:D10,MATCH(TRUE,D1:D10<>"",0))

Array entered

[Tony Valko]

You can do it using the logic of finding the Boolean FALSE but it sounds like yours didn't work because the cells contain formula blanks "".

Try it like this:

=INDEX(A1:A10,MATCH(FALSE,A1:A10="",0))

Another one:

=INDEX(A1:A10,MATCH(TRUE,ISNUMBER(A1:A10),0))

Both are still array formulas.

[Marty3280]

Thanks Ace and Tony. I did try those and same thing. No luck. Formula Test.xlsx

I set up this little spreadsheet to test what I'd like to do. Different workbook, typed everything in manually. Same result. Hopefully you will be able to see it. I'd appreciate any comments if I'm doing anything wrong.

Thanks again,
Marty

[Tony Valko]

It looks like you want to extract each number from a column and concatenate them into a text string?

If that's the case and you have more than a couple of numbers then a VBA function would be needed.

I'm not much of a programmer so someone else will need to help you with that.

[Marty3280]

Thanks again Tony. I was afraid that to do this more simply, VBA would be needed. I am not a programmer either. I think I can tackle this with a really long if statement though. That's what I wanted to avoid.

Thanks again,
Marty

[Tony Valko]

Try posting this question in the Excel Programming / VBA / Macros forum.

Include a link to this thread so folks can see what has already been discussed.