Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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?
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 74
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 74
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
lookup on range (A2 and D2:D30) LFNFan Excel Worksheet Functions 1 September 24th 09 02:22 PM
Lookup within a range Carissa Excel Worksheet Functions 3 June 2nd 09 12:58 AM
Lookup range help Tom Excel Discussion (Misc queries) 2 October 27th 08 10:10 PM
help with lookup and range Mona Excel Worksheet Functions 3 January 11th 08 12:48 AM
Range Lookup Jake Excel Discussion (Misc queries) 2 April 28th 05 11:02 PM


All times are GMT +1. The time now is 10:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"