If value in column A exists between values in column B and C

Closed
Jaye - May 29, 2016 at 09:03 PM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Jun 2, 2016 at 04:23 AM
Hello,

Looking for assistance on the following:

I want to know if value 302000000 (column A) exists between value range 382777400 (column B) and 382777499 (column C) returning either Yes or No.

Appreciate the help!

4 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
May 30, 2016 at 08:34 AM
Hello Jaye,

An If/And formula should be enough to do the task.

In cell C1 (or C2 if you have headings), place the following formula:-

=IF(AND(A1>=B1,A1<=C1),"Yes","No")

and drag it down as far as you need.

If you have headings, then change the cell references to row 2 (A2,B2 etc.)

I hope that this helps.

Cheerio,
vcoolio.
1
Hello vcoolio - Yes it does help..was wondering what if I wanted to search the value 302000000 (column A) across a number of range's?

Example:
In columns B2, C2 there is a value range FROM 382777400 TO 382777499
then below that B3, C3 shows range FROM 882777400 TO 882777499, and so on and so forth?

(hope that makes sense)

Cheers,
Jaye
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
May 31, 2016 at 05:54 AM
Hello Jaye,

You mean like this:-

https://www.dropbox.com/s/y9eej6muuav4875/Jaye%20%28IF%20AND%20Formula%29.xlsx?dl=0

You'll see the formula dragged down in Column D.

Also, my apologies. In my first post I should have said that the formula needs to go in Column D not C.

With the formula, it shouldn't matter what Column A value you are searching for between Columns B & C.

Cheerio,
vcoolio.
0
Hey vcoolio,

Yes, but then searching the same value in A1 across range in B2 and C2, then B3 and C3 and so on and so forth, would I have to create a name range for columns B and C or B and C together, so the value A1 can be searched across however many rows I have?

Hope that makes sense, and really appreciate your help!! :)

Cheers,
Jaye
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Jun 2, 2016 at 04:23 AM
Hello Jaye,

As long as you drag the formula down Column D as far as you need, you won't need to worry about named ranges.

If the value 302000000 will only ever be in cell A1, you can use an absolute reference for the value in the formula as follows:-

=IF(AND($A$1>=B1,$A$1<=C1),"Yes","No")


It will thus always reference A1.

Cheerio,
vcoolio.
0