Last TEXT entry, excluding ""

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

headtoadie

Board Regular
Joined
Aug 1, 2003
Messages
219
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
Joined
Feb 16, 2002
Messages
8,459
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

Aladin Akyurek said:
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..... :LOL:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,100
Messages
5,768,097
Members
425,453
Latest member
bince

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
Top