Lookup across a Range
 

I do lots of vlookups, but I have encountered a problem of a different
shape.

The piece of data that I am looking for may occur anywhere within a 12
x 16 range (B3:M18). And all I need is to confirm its presence or lack
thereof.

So, look for the value from A1 and check the range b3:m18, if found
TRUE, else FALSE.

Which function do I need to do this? Is there one?

Lookup across a Range
 

On Mon, 12 Sep 2011 13:16:22 -0700 (PDT), Commish wrote:

I do lots of vlookups, but I have encountered a problem of a different
shape.

The piece of data that I am looking for may occur anywhere within a 12
x 16 range (B3:M18). And all I need is to confirm its presence or lack
thereof.

So, look for the value from A1 and check the range b3:m18, if found
TRUE, else FALSE.

Which function do I need to do this? Is there one?

This formula must be **array-entered**:

=OR(A1=B3:M18)

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

Lookup across a Range
 

On Sep 12, 4:37*pm, Ron Rosenfeld wrote:
On Mon, 12 Sep 2011 13:16:22 -0700 (PDT), Commish wrote:
I do lots of vlookups, but I have encountered a problem of a different
shape.

The piece of data that I am looking for may occur anywhere within a 12
x 16 range (B3:M18). And all I need is to confirm its presence or lack
thereof.

So, look for the value from A1 and check the range b3:m18, if found
TRUE, else FALSE.

Which function do I need to do this? Is there one?

This formula must be **array-entered**:

=OR(A1=B3:M18)

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. *If you did this
correctly, Excel will place braces {...} around the formula.

That works...

However, I was using the lookup as the first part of an if/then/else.
Can I use an array formula in the middle of an if/then statement?

Lookup across a Range
 

On Mon, 12 Sep 2011 14:30:41 -0700 (PDT), Commish wrote:

That works...

However, I was using the lookup

Huh? What lookup. You only asked for a function that would return TRUE or FALSE depending on the presence of a particular token in a range.

as the first part of an if/then/else.

Can I use an array formula in the middle of an if/then statement?

In general you can. You must enter the entire formula as an array.

Lookup across a Range
 

Ron Rosenfeld wrote on 12-09-11 :

This formula must be **array-entered**:

=OR(A1=B3:M18)

No need of "OR":

{=(A1=B3:M18)}
FormulaArray

Bruno

Lookup across a Range
 

On Wed, 28 Sep 2011 20:55:14 +0200, Bruno Campanini wrote:

Ron Rosenfeld wrote on 12-09-11 :

This formula must be **array-entered**:

=OR(A1=B3:M18)

No need of "OR":

{=(A1=B3:M18)}
FormulaArray

Bruno


I do not believe that is correct.

An array formula, entered in a single cell, will only return the first element of the array. The other elements are accessible, but not displayed and will be ignored by formulas that depend on whether the result is TRUE or FALSE.

The OP wanted to return TRUE if A1 was found anyplace within the range B3:M18. Your formula will only return TRUE if A1=B3 and would return FALSE if, for example A1< B3 but A1=C3

Lookup across a Range
 

Ron Rosenfeld explained :
On Wed, 28 Sep 2011 20:55:14 +0200, Bruno Campanini
wrote:

Ron Rosenfeld wrote on 12-09-11 :

This formula must be **array-entered**:

=OR(A1=B3:M18)

No need of "OR":

{=(A1=B3:M18)}
FormulaArray

Bruno


I do not believe that is correct.

An array formula, entered in a single cell, will only return the first
element of the array. The other elements are accessible, but not displayed
and will be ignored by formulas that depend on whether the result is TRUE or
FALSE.

The OP wanted to return TRUE if A1 was found anyplace within the range
B3:M18. Your formula will only return TRUE if A1=B3 and would return FALSE
if, for example A1< B3 but A1=C3

You are right!
Sorry

Bruno