Mark Worthington said:
Aladin,
Thanks for the input, very clever, forcing a failed lookup to return
the last value.
I have spent some time looking at this, and while I know the formulae
you gave were specific to the question, the following may be helpful
to others like me who browse these Q&A :
No, it's not. The questions these formulas tackle have the following form:
[1]
Last numerical value in a given ref, where ref can be definite like C4:C100
or indefinite like A:A...
The formula is:
=LOOKUP(9.99999999999999E+307,Ref)
Position of the last numerical value within a given ref, where ref can be
definite like C4:C100 or indefinite like A:A...
The formula is:
=MATCH(9.99999999999999E+307,Ref)
[2]
Last text value in a given ref, where ref can be definite like C4:C100 or
indefinite like A:A...
The formula is:
=LOOKUP(REPT("z",255),Ref)
Position of the last text value within a given ref, where ref can be
definite like C4:C100 or indefinite like A:A...
The formula is:
=MATCH(REPT("z",255),Ref)
These give the #N/A error if no number or text exist, depending on the
formula used.
Exactly what we should like to have... A diagnostic value that tells us:
There is no text/numerical value in the ref of interest.
Also, the position will be incorrect if the list does
not start on Row 1 (for example, a selection other than C:C, say
C4:C100).
Definitely not true. All depends on what one wants: the native position or
within ref position.
If the ref of interest is C4:C100...
=MATCH(9.99999999999999E+307,C4:C100)
will give the position of the last numerical value within C4:C100, that is,
a position relative to C4:C100. If you want it to be expressed relative to
column C, which I refer to as the native position, you need to expand...
=MATCH(9.99999999999999E+307,C4:C100)+CELL("Row",C4)
The Index/Match gives a reference (unlike Lookup) so can use Row(..)
etc to find the location/address. Therefore, an alternative method to
find the last numerical or text value, the following can be used :
=IF(COUNT(Data)=0,INDEX(Data,MATCH(REPT("z",255),Data)),
IF(COUNTA(Data)-COUNT(Data)=0,INDEX(Data,MATCH(9.99999999999999E+307,Data)),
INDEX(Data,MAX(MATCH(REPT("z",255),Data),MATCH(9.99999999999999E+307,Data)))
))
I'd use since you have a definite ref (Data does not refer to whole column)
for the last value either text or numerical (not: last logical or error
value) something like...
=LOOKUP(9.99999999999999E+307,1/(1-(ISLOGICAL(Data)+ISERROR(Data)+ISBLANK(Da
ta))),Data)
Note that the last formula is restricted to definite refs and expensive,
although less expensive than one you suggest, while [1] and [2], addressing
the last numerical or the last text value are efficient and insensitive to
irrelevant type of data points plus empty cells.
If Data would be a whole column type of ref, say A:A, one could use multiple
cells...
F1:
=MATCH(9.99999999999999E+307,A:A)
F2:
=MATCH(REPT("z",255),A:A)
F3:
=CHOOSE(COUNT(F1:F2)+1,"",SUMIF(F1:F2,"<>#N/A"),MAX(F1:F2))
In F4 you could then fetch the value of interest:
=IF(N(F3),INDEX(A:A,F3),"")
