If value in column A exists between values in column B and C
Closed
Jaye
-
May 29, 2016 at 09:03 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jun 2, 2016 at 04:23 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jun 2, 2016 at 04:23 AM
Related:
- If value in column A exists between values in column B and C
- Excel vba check if value exists in list - Guide
- Display two columns in data validation list but return only one - Guide
- How to delete column in word - Guide
- Tweetdeck remove column - Guide
- Vba create folder if not exist ✓ - Excel Forum
4 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 30, 2016 at 08:34 AM
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.
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.
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
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
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 31, 2016 at 05:54 AM
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.
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
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
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 2, 2016 at 04:23 AM
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.
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.
