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 !

 

[PATSYS]

If only one value in the range:

=VLOOKUP("X",A1:A10,1,TRUE)

 

[Jon von der Heyden]

I was going to suggest:

=VLOOKUP("*",A1:A10,1,0)

But neither of our suggestions seems to work if the range contain a numeric value - but ok if numbers entered as text.

But I reckon you've probably thought of this already...

Jon

 

[Greg Truby]

Tom,

This seems to work...<ul>[*]=IF(COUNT(TestRange),VLOOKUP(9E+99,TestRange,1),IF(COUNTA(TestRange),VLOOKUP(REPT("z",255),TestRange,1),"she's empty, boss"))[/list]

 

[PATSYS]

To address the numeric value:

=IF(ISNA(VLOOKUP("X",A1:A10,1,TRUE))=FALSE,VLOOKUP("X",A1:A10,1,TRUE),VLOOKUP(9999999,A1:A10,1,TRUE))

 

[Aladin Akyurek]

Tom,

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)

2] Any last numeric or text value

=INDEX(A1:A10,LOOKUP(9.9999999999999E+307,CHOOSE({1,2},MATCH(9.99999999999999E+307,A1:A10),MATCH(REPT("z",255),A1:A10))))

While these need no CSE, they are nevetheless array-grinders. The latter is so because of the CHOOSE call.

 

[Greg Truby]

Hi Aladin,

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

 

[Domenic]

I wonder how the following would fare?

=INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100<>"",0,0),0))

 

[Aladin Akyurek]

I wonder how the following would fare?

=INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100<>"",0,0),0))

That's also an array grinder and it will pick up any last value which is not a formula blank.

 

[Domenic]

I wonder how the following would fare?

=INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100<>"",0,0),0))

That's also an array grinder and it will pick up any last value which is not a formula blank.

I thought so. Thanks "Teach"...