Non array formula to return first value in a range

[Tom Urtis]

OK, am I missing something obvious.

Range A1:A10 contains no formulas, only constants.
Only one constant value at most would ever be in that range, sometimes nothing in that range.
The value might be text like "Tom", or it might be a number like "1234".
The value could be in any of the 10 cells.

So far, and this is where the "missing something obvious" part comes in, I have only seen array solutions such as
=INDEX(A1:A10,MATCH(TRUE,A1:A10<>"",0))

My question is, can this result be achieved with a non array / non sumproduct formula.

I need to do this for 6000 cells in a project, that's the design and that's the way it is. I don't want that many arrays in the workbook. I already did this with VBA so I am not asking for a VBA or UDF solution, just hoping to know from the formula wizards what a non-array formula could be that returns the first found (and in this case only-existing) constant value in a range.

Thanks !

 

[Aladin Akyurek]

Hi Aladin,

Are the formulae you posted going to be less expensive than the COUNT/COUNTA & VLOOKUP combo I posted?

No idea, although it counts 6 function calls. I think it's a good score like Lookup(2,1/(...),...).

 

[VoG]

Just to show my ignorance of formulas, I understand the syntax for LOOKUP to be

=LOOKUP(WhatToLookFor, RangeToLookIn, RangeToPickFrom)

... so how does Lookup(2,1/(...),...) work please

 

[Domenic]

Just to show my ignorance of formulas, I understand the syntax for LOOKUP to be

=LOOKUP(WhatToLookFor, RangeToLookIn, RangeToPickFrom)

... so how does Lookup(2,1/(...),...) work please

Have a look at Aladin's explanation here...

 

[VoG]

Thank you very much Domenic.

It may take a third or fourth read before it sinks in

 

[Tom Urtis]

Thanks so much you guys for helping me out.

Aladin correctly pointed out...

Since there will be either no value or just one (text or numeric) value, we can invoke a last value formula...

1] Any last value

=LOOKUP(2,1/(A1:A10<>""),A1:A10)

...so in my case because there would be at most one value in the range, I did not have to worry about a formula to find the first value, contrary to how I framed my original question.


The formulas that correctly returned the requested results were

Greg's:
=IF(COUNT(TestRange),VLOOKUP(9E+99,TestRange,1),IF(COUNTA(TestRange),VLOOKUP(REPT("z",255),TestRange,1),"she's empty, boss"))


both of Aladin's:
=LOOKUP(2,1/(A1:A10<>""),A1:A10)
and
=INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100<>"",0,0),0))


and Domenic's:
=INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100<>"",0,0),0))


The "array grinder" metaphor indeed was my primary consideration here, as some non-CSE formulas can still have the overhead of arrays and slow the workbook almost or as much as if they were arrays.

So now I have what I need. In this project, the formulas are triggered by a VBA-induced event. I'll do some tests to see if these formulas are a better deal for my project than my in-place alternative that uses VBA to do everything.

Thanks again everyone, much appreciated.

 

[Domenic]

I may be mistaken, but in an informal test it looks like INDEX/MATCH is a little bit more efficient than LOOKUP. Probably because INDEX/MATCH doesn't have to 'divide by the array of values'...