Last TEXT entry, excluding ""

[Brian from Maui]

I've been using =INDEX(A:A,MATCH(REPT("z",90),A:A)) to find the last text entry. Now column A has a IF filter that returns a blank(""). I'd like the last text entry excluding the "".

 

[Aladin Akyurek]

I've been using =INDEX(A:A,MATCH(REPT("z",90),A:A)) to find the last text entry. Now column A has a IF filter that returns a blank(""). I'd like the last text entry excluding the "".

The following fancy formula allows you to use A:A as reference...

=INDEX(A:A,MAX(INDEX((A2:INDEX(A:A,MATCH(REPT("z",255),A:A))<>"")*ROW(A2:INDEX(A:A,MATCH(REPT("z",255),A:A))),0,1)))

which must be confirmed with constrol+shift+enter instead of just enter.

Book2
	A	B	C	D
1
2	vfa		trq
3	cxa
4	sza
5
6
7	trq
8
9
10
Sheet1

 

[headtoadie]

If the row you want will always be the row before the one containing "" AND "" is always the last row. Then this may work

Where "A" is the column and -1 is the number of rows to go backward to the one you want

=OFFSET(INDIRECT("A"&MATCH(REPT("z",90),A:A)),-1,0)


HT

Edit: Just saw Aladin's solution, looks like I misunderstood the problem

 

[Brian from Maui]

Aladin,

Thank-you!

I'm assuming if I were to use this for a numeric entry, I'll just use 9.999... or BigNum?

Headtoadie,

No the row is not always the row before...thanks!

 

[Brian from Maui]

Also, performance wise, should I define a range instead of using A:A?

 

[Aladin Akyurek]

...I'm assuming if I were to use this for a numeric entry, I'll just use 9.999... or BigNum?...

=LOOKUP(BigNum,A:A)

will always give you the last numeric value in A if one exists, irrespective of formula-blanks, logical or error values. So, no need for any fancy formula for that.

 

[Brian from Maui]

...I'm assuming if I were to use this for a numeric entry, I'll just use 9.999... or BigNum?...

=LOOKUP(BigNum,A:A)

will always give you the last numeric value in A if one exists, irrespective of formula-blanks, logical or error values. So, no need for any fancy formula for that.

Thank-you again.....

 

[Aladin Akyurek]

Also, performance wise, should I define a range instead of using A:A?

That is a bit different, that is, you need to treat definite ranges differently... And we won't be less expensive.

Book2
	A	B	C	D
1
2	vfa		trq
3	cxa		trq
4	sza
5
6
7	trq
8
9
10
Sheet1

The formula in C3 is:

=INDEX($A$2:$A$9,MAX(ISTEXT(A2:A9)*(A2:A9<>"")*ROW(A2:A9))-CELL("Row",A2)+1)

which must be confirmed with control+shift+enter instead of just with enter.

If you have anything else unrelated below the definite range of A2:A9, the foregoing formula is necessary.

 

[Brian from Maui]

OK....Understood!

 

[Aladin Akyurek]

Another approach for fetching the last non-blank (<>"") text value...

aaLastNonBlankTextValue Brian from Maui.xls
	A	B	C	D	E	F	G
1			Definitereference		IndefiniteReference
2	vfa		A2:A9		A:A
3	cxa		LastNon-BlankTextValue		LastNon-BlankTextValue
4	sza
5			trq		trq
6					trq
7	trq
8
9
10
Sheet2

C5:

=LOOKUP(9.99999999999999E+307,1/(1-(A2:A9="")),A2:A9)

Retrieves the last non-blank text value from a definite reference.

E5:

=LOOKUP(9.99999999999999E+307,1/(1-(A2:INDEX(A:A,65536)="")),A2:INDEX(A:A,65536))

Retrieves the last non-blank text value from an (almost) indefinite reference.

E6:

=LOOKUP(9.99999999999999E+307,1/(1-(A2:INDEX(A:A,MATCH(REPT("z",255),A:A))="")),A2:INDEX(A:A,MATCH(REPT("z",255),A:A)))

Retrieves the last non-blank text value from an indefinite reference, rendered definite by the MATCH bit.