LOOKUP with Booleans

[[email protected]]

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

[KL]

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


<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

KL wrote:
> 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
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

[KL]

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


<[email protected]> wrote in message
news:[email protected]...
> 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
>
> KL wrote:
>> 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
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>