See if cell value appears within a value range given by another cell.

[Pierre]

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

 

[Per Jessen]

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

 

[Pierre]

Per: You're a genius!

I knight you "Sir Per".
Thank you.
Pierre

 

[Pierre]

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

 

[Gord Dibben]

Did you copy down to A10000 as Per suggested?


Gord Dibben MS Excel MVP

 

[Pierre]

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

 

[Per Jessen]

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

 

[Pierre]

Per,
Thank you for the reply and adding the column formula. Will try later
today and report back. Thanks again for your interest.

Pierre

 

[Pierre]

Still unable to get it to behave. Where I'd expect a "No", a On Apr

 

[Pierre]

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