# Need help with formula that doesn't find numbers

#### Russk68

##### Active Member
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))),"")

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### RoryA

##### MrExcel MVP, Moderator
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

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

Thanks!

#### RoryA

##### MrExcel MVP, Moderator
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

##### Well-known Member

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

##### Active Member
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

##### Well-known Member

(regular formula)

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

#### Russk68

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

#### Russk68

##### Active Member
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

##### MrExcel MVP, Moderator
That formula will not find formulas that return "" so what are the formulas actually returning?

Replies
8
Views
106
Replies
4
Views
48
Replies
13
Views
248
Replies
2
Views
25
Replies
7
Views
119