# Last TEXT entry, excluding ""

#### Brian from Maui

##### MrExcel MVP
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 "".

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

##### MrExcel MVP
Brian from Maui said:
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
ABCD
1
2vfatrq
3cxa
4sza
5
6
7trq
8
9
10
Sheet1

##### Board Regular
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

##### MrExcel MVP

Thank-you!

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

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

#### Brian from Maui

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

##### MrExcel MVP
Brian from Maui said:
...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

##### MrExcel MVP
Brian from Maui said:
...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.....

##### MrExcel MVP
Brian from Maui said:
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
ABCD
1
2vfatrq
3cxatrq
4sza
5
6
7trq
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

##### MrExcel MVP
OK....Understood!

##### MrExcel MVP
Another approach for fetching the last non-blank (<>"") text value...
aaLastNonBlankTextValue Brian from Maui.xls
ABCDEFG
1DefinitereferenceIndefiniteReference
2vfaA2:A9A:A
3cxaLastNon-BlankTextValueLastNon-BlankTextValue
4sza
5trqtrq
6 trq
7trq
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.

Replies
7
Views
110
Replies
3
Views
210
Replies
2
Views
330
Replies
1
Views
224
Replies
6
Views
174

1,187,174
Messages
5,962,036
Members
438,576
Latest member
Cstewart29

### 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.

### Which adblocker are you using?

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

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