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 "".
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 "".
Book2 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ||||||
2 | vfa | trq | ||||
3 | cxa | |||||
4 | sza | |||||
5 | ||||||
6 | ||||||
7 | trq | |||||
8 | ||||||
9 | ||||||
10 | ||||||
Sheet1 |
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?...
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.
Brian from Maui said:Also, performance wise, should I define a range instead of using A:A?
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 |