Hello All

Still can't get this to behave. Added column D formulaWhere I'd
expect a "No" to appear in col C, FALSE is showing up. It's still not
looking down column A for either the existing value or if the value
falls within any of the ranges presented somewhere column A.

Any help is appreciated.
Pierre

On Apr 1, 6:18*pm, Per Jessen wrote:
Pierre,

My formula can *only* evaluate row by row. Use my original formula in
C1 and copy it down to C10000, now you have yes on now for each line.

Then insert this formula in D1 to calculate 'one' yes or no (column C
can be hidden):

=IF(COUNTIF(C1:C10000,"=Yes")=1,"Yes","No")

/Per

On 1 Apr., 22:34, Pierre wrote:



Gord,: I copied it down. *As Per said, it'll find a match if the
contents in B1 meet the criteria in A1. *That works great.
What I'm looking for is to see it it'll match B1 with any range in
A1:a10000. *I copied down the formulas in column C, got either
#VALUE!, or "No".
Thanks.

Pierre

.On Apr 1, 3:01*pm, Gord Dibben <gorddibbATshawDOTca wrote:

Did you copy down to A10000 as Per suggested?

Gord Dibben *MS Excel MVP

On Thu, 1 Apr 2010 12:39:21 -0700 wrote:
Per, can it be adjusted to find a "Yes", or "No", if the value is
found (or not found) within all the cells in the entire column, not
just the range in A1? *Lets say A2:A10000?
I tried replacing it with that and it didn't behave.

Thanks for your thoughts on this.

Pierre
On Apr 1, 1:56 pm, Per Jessen wrote:
Hi

This formula will return Yes if the number in B1 is in the range given
in A1.

=IF($B$1=LEFT(A1,FIND("-",A1)-1)*1,IF($B
$1<=MID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))

The formula can be copied down as required.

Hopes this helps.
...
Per

On 1 Apr., wrote:

In one column (b), have the figure of 470
In another column (a), a cell contains values containing a number, a
dash and another number: "425 - 490"
I need to check to determine if the number 470 is found within a range
defined by a cells contents. In this case, yes, it's equal to or
between them.

(The dash may or may not have spaces around it. ..could be 425-490,
or 425 - 490.)
The column (b) item can be the 425, or the 490 and any number in
between.

Need to do a vlookup to find if the 470 is present in any of the
assorted ranges contained in column (a).

Thanks for your interest.
Pierre- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Hide quoted text -

- Show quoted text -