Need help with formula that doesn't find numbers

Russk68

Active Member
Joined
May 1, 2006
Messages
473
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))),"")
 

Some videos you may like

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
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
May 1, 2006
Messages
473
Hi Rory,
I am looking for the first value in the range wether it's text or number.

Thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Jul 2, 2012
Messages
4,517

ADVERTISEMENT

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
Joined
May 1, 2006
Messages
473
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
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

How about
(regular formula)


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

Russk68

Active Member
Joined
May 1, 2006
Messages
473
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
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That formula will not find formulas that return "" so what are the formulas actually returning?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top