Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
madalina69 la Simpatie.ro
 | Femeie
23 ani
Iasi
cauta Barbat
23 - 35 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
Sources:
What I need to do is to return the month text for a date that I have in a range in column G. I must find out the month for which I have to pay some bills. So I need to look for the last value in column F, then display the month for the date in column G.
Here it goes:
=LOOKUP(2;1/(F5:F16<>"");TEXT(G5:G16;"mmmm"))
The emboldened part actually looks for the last value in interval F5:F16. The last part of the formula can be replaced with whatever value you want.
I'll reproduce below more formulas from those sources above:
For numbers, you can use: =VLOOKUP(9.99999999999999E+307;B:B;1)
Last numeric value also: =LOOKUP(2;1/(ISNUMBER(A1:A100));A1:A100)
Another option for text/numbers is to use: =INDEX(A:A;COUNTA(A:A);1)
If there's an error in the last non-empty cell: =LOOKUP(2;1/(NOT(ISBLANK(A:A)));A:A)
Position of the last value (row number): =LOOKUP(2;1/(A:A<>"");ROW(A:A))
_______________________________________
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
TRaP a scris:
Source:
=LOOKUP(2;1/(A:A<>"");A:A)
It does not have to be an entire column, you can make the formula like:
=LOOKUP(2;1/(D2:D30<>"");D2:D30) |
TRaP a scris:
Same source as above, all credit to them.
Dealing with errors
If there are errors in the lookup_vector, particularly if there is an error in the last non-empty cell, this formula needs to be adjusted. This adjustment is needed because <>"" criteria will return an error itself if a cell contains an error. To workaround this problem, use ISBLANK with NOT:
=LOOKUP(2;1/(NOT(ISBLANK(A:A)));A:A)
Last numeric value
To get the last numeric value, you can add the ISNUMBER function like this:
=LOOKUP(2;1/(ISNUMBER(A1:A100));A1:A100)
Position of the last value
If you want to get the position (in this case row number) of the last value, you can try a formula like this:
=LOOKUP(2;1/(A:A<>"");ROW(A:A))
Here we feed the row numbers of the same range into lookup for the result vector and get back the row number of the last match. |
_______________________________________
|
|
| pus acum 10 luni |
|