If value in column A exists between values in column B and C
Closed
Jaye
-
29 May 2016 à 21:03
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 - 2 Jun 2016 à 04:23
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 - 2 Jun 2016 à 04:23
Related:
- If value in column A exists between values in column B and C
- How to delete a column in word - Guide
- Excel column number - Guide
- Tweetdeck expand column - Guide
- Vba check if value exists in array - Guide
- Excel count occurrences in column - Guide
4 responses
vcoolio
Posts
1411
Registration date
Thursday 24 July 2014
Status
Contributor
Last seen
6 September 2024
262
30 May 2016 à 08:34
30 May 2016 à 08:34
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 24 July 2014
Status
Contributor
Last seen
6 September 2024
262
31 May 2016 à 05:54
31 May 2016 à 05:54
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 24 July 2014
Status
Contributor
Last seen
6 September 2024
262
2 Jun 2016 à 04:23
2 Jun 2016 à 04:23
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.