LOOKUP with Booleans

[landen99]

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
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 and the values to
return in [A]
the 0 argument in the MATCH function means that the array is not sorted

Regards,
KL

 

[landen99]

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,

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

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,

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