LOOKUP with Booleans

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

landen99@gmail.com - 14 Sep 2005 21:06 GMT

Hi everyone,
I'm trying to figure out some wierd LOOKUP behavior with booleans. I
need to create a lookup function which looks for a TRUE in a range and
picks the value from the same row different column.
It would look like this:

1 FALSE
2 TRUE
3 FALSE
4 FALSE
5 FALSE
6 FALSE

The desired return value would be "2" in this case.

I just learned that the values in the lookup vector must be arranged in
ascending order, and these booleans are not arranged that way (if TRUE
= 1 and FALSE = 0).

After playing around with LOOKUP, I noticed some funny results with
other combinations of values in the lookup vector. LOOKUP looksup the
first true in rows 3-6, otherwise it returns the last false in rows
3-6. If rows 3-6 have neither TRUE nor FALSE, then it returns the
first TRUE in rows 1-2, otherwise it returns the last false in rows
1-2. This doesn't make much sense to me, so I'm totally at a loss
concerning how to overcome it.

Does anyone have any idea what's going on here? Does anyone have a
better idea for accomplishing this same task? Thank you for your help.

Andy Landen
SLC, UT

Reply to this Message

KL - 14 Sep 2005 21:27 GMT

Hi,

You should not use LOOKUP unless your values are sorted in ascending orderas
indicated in Help article you have mentioned (an exception to that is when
you want to return the last value in the array)

Instead you could use VLOOKUP if the value to return is in a column
following the one with the booleans, e.g.:

=VLOOKUP(TRUE,A1:B6,2,0)
this formula assumes that the booleans are in column [A] and the values to
return in [B]
the 0 argument in the VLOOKUP function corresponds to FALSE and means that
the array is not sorted

Or in any situation you can use:

=INDEX(A1:A6,MATCH(TRUE,B1:B6,0))
this formula assumes that the booleans are in column [B] and the values to
return in [A]
the 0 argument in the MATCH function means that the array is not sorted

Regards,
KL

> Hi everyone,
> I'm trying to figure out some wierd LOOKUP behavior with booleans. I
[quoted text clipped - 28 lines]
> Andy Landen
> SLC, UT

Reply to this Message

landen99@gmail.com - 14 Sep 2005 22:24 GMT

Thank you very much for your help. I had to group my values together
to form a table for the "A1:B6" part. Couldn't have the columns
separated for some reason. Anyhow, the VLOOKUP works perfectly, thanks
again.

Andy

> Hi,
>
[quoted text clipped - 53 lines]
> > Andy Landen
> > SLC, UT

Reply to this Message

KL - 15 Sep 2005 07:14 GMT

Hi,

You don't need to group the columns - they can be at any distance from each
other.

for VLOOKUP the additional requirement is that the column with the results
is located to the right frome the evaluated column

=VLOOKUP(SearchedValue,LookupTable,ResultColumnNumber,Sorted)

the second formula does not have this requirement.

=INDEX(ResultColumnReference,MATCH(SearchedValue,LookupColumnReference,Sorted))

Regards,
KL

> Thank you very much for your help. I had to group my values together
> to form a table for the "A1:B6" part. Couldn't have the columns
[quoted text clipped - 65 lines]
>> > Andy Landen
>> > SLC, UT

Reply to this Message