If value in column A exists between values in column B and C
Closed
Jaye
-
May 29, 2016 at 09:03 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jun 2, 2016 at 04:23 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - 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
- Auto serial number in excel based on another column ✓ - Excel Forum
- How to convert column to row in notepad++ ✓ - Excel Forum
- Based on the values in cells b77 b81 - Office Software Forum
- Fixing populating values into textboxes based on showing data in listbox ✓ - Excel Forum
4 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
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
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
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
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
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.
