Lookup across a Range

[Commish]

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?

 

[Ron Rosenfeld]

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.

 

[Commish]

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?

 

[Ron Rosenfeld]

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.

 

[Bruno Campanini]

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

 

[Ron Rosenfeld]

Ron Rosenfeld wrote on 12-09-11 :


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

 

[Bruno Campanini]

Ron Rosenfeld explained :

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