My interpretation is that for a match of "2" to happen, "2" itself has to
be in the array. The result of a calculation being "2" won't cause a match
to happen. But I am not comfortable with this and I haven't totally
convinced myself.<<

Try using these values
A1: 9
A2: 8
A3: 7
A4: 6
A5: 5
A6: 0.5
A7: 3

This formula: =LOOKUP(2,1/A1:A100,A1:A100) returns 0.5
Consequently, I'm not so comfortable with "may be safe".

=LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100)
always returns the last non-zero, non-blank, non-error value (assuming you
don't want a final error value returned).

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Epinn" wrote:

Previously I wrote:

Afterall, the following formula may be "safe" if we want to ignore zero.

=LOOKUP(2,1/A1:A100,A1:A100)

I want to add ".... and text."

This formula is only good when the array has *numbers only*.

I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know.

(A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100)

(B) =LOOKUP(2,1/(A1:A100<""),A1:A100)

Epinn

"Epinn" wrote in message ...
Ron,

Thank you for your response.

I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later.

I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used.

Would you prefer this formula if "0" is to be ignored?

=LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100)

a last value of zero in the lookup range would be ignored because the
resulting fraction would be an error ......

"Evaluate formula" shows #DIV/0! and therefore ignored.

If the lookup range is in the right order and happens to contain 0.5 in the
right place, 1/0.5 would calculate to 2, resulting in a match.

I included 0.5 in the array and I sorted it as well.

However, the following formula did *NOT* find a match and still returned the last value.

=LOOKUP(2,1/A1:A100,A1:A100)

My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself.

Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here?

Afterall, the following formula may be "safe" if we want to ignore zero.

=LOOKUP(2,1/A1:A100,A1:A100)

Epinn

"Ron Coderre" wrote in message ...
Did you mean to use this version?:
=LOOKUP(2,1/(A1:A100<""),A1:A100)

In that formula, this section:(A1:A100<"")
returns a series of 1's(for non-blanks) and 0's(for blanks)...or error
values (for cells that contain errors)

Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and
errors for blanks or errors.
Since there will be no 2's for the LOOKUP to find, it will match on the last
non-error value (LOOKUP ignores errors in the lookup range) and return the
corresponding value.

That version has advantages over the formula you posted:
=LOOKUP(2,1/A1:A100,A1:A100)

Among them are these:
If the lookup range is in the right order and happens to contain 0.5 in the
right place, 1/0.5 would calculate to 2, resulting in a match.
OR a last value of zero in the lookup range would be ignored because the
resulting fraction would be an error.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Epinn" wrote:

Hi,

=LOOKUP(2,1/A1:A100,A1:A100)

The following is my interpretation of this formula. If I am wrong, please correct me.

I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5.

Thanks for your help.

Epinn