return the second text value

[Rubber Beaked Woodpecker]

Hi

I use the following formula to return the first text value from a range.

{=INDEX(AB9:AB24,MATCH(TRUE,ISTEXT(AB9:AB24),0))}

My question is, would it be possible to modify the formula to return the second text value from the same range?

Many thanks

RBW

 

[Rubber Beaked Woodpecker]

Hi

I use the following formula to return the first text value from a range.

{=INDEX(AB9:AB24,MATCH(TRUE,ISTEXT(AB9:AB24),0))}

My question is, would it be possible to modify the formula to return the second text value from the same range?

Many thanks

RBW

OK

It appears this is the solution {=INDEX(AB9:AB24,MATCH(TRUE,ISTEXT(AB9:AB24),1))}

However this now brings me to my next problem in how to return the third text value!

This possible please?

RBW

 

[Proulxs]

Hi,
You could use a first Match to find the number of the first line with text then use that in addition of your starting line (9th) to make an adresse used in the following match so it start at the line under the first result) like so;
{=INDEX(AB9:AB24,MATCH(TRUE,ISTEXT(INDIRECT(ADDRESS(MATCH(TRUE,ISTEXT(AB9:AB24),0)+9;28))),0))}

Untested might have to tweak numbers by 1 or 2...
Edit to clarify: The +9 in the formula refer to your first line of range (AB9) then the 28 refer to the column: AB

 

[Scott Huish]

Perhaps:
=INDEX(AB:AB,SMALL(IF(ISTEXT($AB$9:$AB$24),ROW($AB$9:$AB$24),""),ROW(A1)))

Copy down.

textvalues.xlsx
	AB	AC	AD
9	abc		abc
10			def
11			ghi
12	def
13
14
15	ghi
16
Sheet1

Cell Formulas
Range	Formula
AD9:AD11	AD9	=INDEX(AB:AB,SMALL(IF(ISTEXT($AB$9:$AB$24),ROW($AB$9:$AB$24),""),ROW(A1)))

 

[bebo021999]

For formula in #4:
Its an array formula, since excel 2016, then need to be confirmed with Ctrl-Shift-Enter combination.

 

[Fluff]

Another option is

=INDEX(AB:AB,AGGREGATE(15,6,ROW($AB$9:$AB$24)/ISTEXT($AB$9:$AB$24),ROWS(A$1:A1)))

dragged down.

 

[Rubber Beaked Woodpecker]

Another option is

=INDEX(AB:AB,AGGREGATE(15,6,ROW($AB$9:$AB$24)/ISTEXT($AB$9:$AB$24),ROWS(A$1:A1)))

dragged down.

Perfect sir! Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.