Need help with formula that doesn't find numbers

[Russk68]

Hi All,
This array formula returns a cell value but not if it's a number. Is it possible to return a cell value as a number or text?

Thank you!

Russ

=IFERROR(INDEX(Distro!$Z$1:Distro!$Z$3244,SMALL(IF(ISTEXT(Distro!$Z$1:Distro!$Z$3244),ROW(Distro!$Z$1:Distro!$Z$3244),""),ROW(Distro!Z1))),"")

 

[RoryA]

THe whole point of that formula appears to be to find the first text value in the range. Are you just looking for the first non-blank value, or something else?

 

[Russk68]

Hi Rory,
I am looking for the first value in the range wether it's text or number.

Thanks!

 

[RoryA]

Then something like:

=IFERROR(INDEX(Distro!$Z$1:Distro!$Z$3244,SMALL(IF(Distro!$Z$1:Distro!$Z$3244<>"",ROW(Distro!$Z$1:Distro!$Z$3244),""),ROW(Distro!Z1))),"")

 

[XOR LX]

Hi,

Although your formula (with SMALL) suggests you're looking for the second, third, etc. non-blank entries as well, what you say would indicate that in fact you only require the first. If so, you can use a shorter array formula**:

=INDEX(Distro!$Z:$Z,MATCH("?*",Distro!$Z$1:$Z$3244&"",0))

Regards

 

[Russk68]

Thank you Rory and XOR LX
I haven't had a chance to try this yet but I will get back when I do!

 

[DanteAmor]

How about
(regular formula)


=INDEX(Distro!$Z$1:$Z$3244,MATCH(TRUE,INDEX((Distro!$Z$1:$Z$3244<>""),0),0))

 

[Russk68]

Hi XOR LX,
I am in fact looking for all entries.
Thanks!

 

[Russk68]

Hi Rory
Your formula does find text as well as numbers in the column. It also finds the blank entries where formulas exist. Is there a way to only return cells with values?
Thank you!

 

[RoryA]

That formula will not find formulas that return "" so what are the formulas actually returning?