If value in column A exists between values in column B and C [Closed]

Report
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
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

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.
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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!