LOOKUP ( )

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Epinn - 16 Oct 2006 00:24 GMT

Hi,

Re: =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

Reply to this Message

Ron Coderre - 16 Oct 2006 02:16 GMT

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

> Hi,
>
[quoted text clipped - 7 lines]
>
> Epinn

Reply to this Message

Epinn - 16 Oct 2006 03:43 GMT

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

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,
>
[quoted text clipped - 7 lines]
>
> Epinn

Reply to this Message

JMB - 16 Oct 2006 04:59 GMT

This seems to work if you wanted to ignore both 0 and blanks

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

> Ron,
>
[quoted text clipped - 74 lines]
> >
> > Epinn

Reply to this Message

Epinn - 16 Oct 2006 05:07 GMT

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)

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" <ronREMOVETHIScoderre@bigfoot.com> wrote in message news:9D5A598B-9C16-427C-87E5-37160E889078@microsoft.com...
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

> Hi,
>
[quoted text clipped - 7 lines]
>
> Epinn

Reply to this Message

Ron Coderre - 16 Oct 2006 18:33 GMT

>>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

> Previously I wrote:
>
[quoted text clipped - 89 lines]
> >
> > Epinn

Reply to this Message

Epinn - 16 Oct 2006 20:42 GMT

Ron,

Guess what, *before* I read your post I tested "0.5" some more. I did pick up 0.5 even though it is *not* the last value. The difference between this time and last time is I sorted the array in ascending order last time but no order this time. Looks like this is temperamental.

Anyway, I agree with you that it may not be "safe" to use
=LOOKUP(2,1/A1:A100,A1:A100)

>> =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). <<

None of the formulae that we have discussed will return error values (e.g. #DIV/0!); so I won't worry about it. JMB is right about checking for zero will take care of ignoring blanks as well. Thanks, JMB.

I have done some more testing and have come to the following conclusion. Please correct me if I am wrong.

=LOOKUP(2,1/(A1:A10<>""),A1:A10)
ignore blank but not 0, not text

=LOOKUP(2,1/(A1:A10<>0),A1:A10)
ignore blank and zero but not text

=LOOKUP(2,1/(ISNUMBER(A1:A10)),A1:A10)
ignore blank and text but not 0

=LOOKUP(2,1/(ISTEXT(A1:A10)),A1:A10)
ignore blank, zero, numbers but not text

I am quite satisfied now unless someone tells me I am wrong on the above. I can include more conditions like ignoring blank, zero and text, but I want to give my brain a rest.

Thank you very much for your guidance, Ron. I am glad that I posted.

Epinn

>>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:
>
[quoted text clipped - 92 lines]
> >
> > Epinn

Reply to this Message

Ron Coderre - 17 Oct 2006 01:41 GMT

Don't forget the old standbys:

The value of the last numeric cell in Col_A:
=LOOKUP(10^99,A:A)

The value of the last text cell in Col_A
=LOOKUP(REPT("z",255),A:A)

***********
Regards,
Ron

XL2002, WinXP

>
> Ron,
[quoted text clipped - 158 lines]
> > >
> > > Epinn

Reply to this Message

Epinn - 17 Oct 2006 23:32 GMT

Ron,

Thank you for the bonus which I always like. Previously, I spotted =LOOKUP(REPT("z",255),A:A) in a 2004 thread and I have almost forgotten about it because I am so focused on this thread. Thank you for reminding me.

I also found the following :-

=LOOKUP(9.99999999999999E+307,C:C)

I don't understand it but I understand yours. So, I'll use =LOOKUP(10^99,A:A).

I want to throw in two more formulae to determine the position ......

=MATCH(REPT("z",255),A:A)
=MATCH(10^99,A:A)

With the bonus formulae, I have discovered that I can't use A:A with the four formulae in my previous post because they return an error. But then if I specify A100 as the last cell of the array and even if it is blank, it will still be okay. I don't understand but I may want to let it go unless someone wants to shed some light on this.

Thanks again.

Epinn

Don't forget the old standbys:

The value of the last numeric cell in Col_A:
=LOOKUP(10^99,A:A)

The value of the last text cell in Col_A
=LOOKUP(REPT("z",255),A:A)

***********
Regards,
Ron

XL2002, WinXP

"Epinn" wrote:

>
> Ron,
[quoted text clipped - 159 lines]
> > >
> > > Epinn

Reply to this Message

Pete_UK - 18 Oct 2006 00:55 GMT

Epinn,

9.99999999999999E+307 is the largest number that Excel can represent.
However, you can use a smaller number as long as it is greater than the
largest number you expect in the data set, so 10^99 will cover most
eventualities. If I want to do something like this for dates, I know
that as long as the number is greater than about 40000, then this will
also work (so, 99999 will do).

Hope this sheds a bit more light.

Pete

> Ron,
>
[quoted text clipped - 193 lines]
> > > >
> > > > Epinn

Reply to this Message

Epinn - 18 Oct 2006 02:10 GMT

All right, I have another bonus - 99999 for dates.

Thanks, Pete.

Epinn

Epinn,

9.99999999999999E+307 is the largest number that Excel can represent.
However, you can use a smaller number as long as it is greater than the
largest number you expect in the data set, so 10^99 will cover most
eventualities. If I want to do something like this for dates, I know
that as long as the number is greater than about 40000, then this will
also work (so, 99999 will do).

Hope this sheds a bit more light.

Pete

Epinn wrote:

> Ron,
>
[quoted text clipped - 192 lines]
> > > >
> > > > Epinn

Reply to this Message