Need help with formula that doesn't find numbers

Russk68

Well-known Member
Joined
May 1, 2006
Messages
589
Office Version
  1. 365
Platform
  1. MacOS
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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?
 
Upvote 0
Hi Rory,
I am looking for the first value in the range wether it's text or number.

Thanks!
 
Upvote 0
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))),"")
 
Upvote 0
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
 
Upvote 0
Thank you Rory and XOR LX
I haven't had a chance to try this yet but I will get back when I do!
 
Upvote 0
How about
(regular formula)


=INDEX(Distro!$Z$1:$Z$3244,MATCH(TRUE,INDEX((Distro!$Z$1:$Z$3244<>""),0),0))
 
Upvote 0
Hi XOR LX,
I am in fact looking for all entries.
Thanks!
 
Upvote 0
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!
 
Upvote 0
That formula will not find formulas that return "" so what are the formulas actually returning?
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top