Finding a number in multiple cells with each of its own range

Hello All,

So this is something that I'm not sure is possible in Excel. I would like to use an "if,then" statement to see if a number is in multiple cells. I know this is usually very simple except there's a catch here. In each cell there's a number range using a "-". So in a single cell a range would be 301-305. I am open to having the range done a different way like 301,302...etc. I just decided this would be an easy way to look at it and was hoping to find a way to solve this problem with leaving in the dash. Whatever is practical is fine with me.

Using Example A in the attached file I want to use this statement, =IF(308 is in any of the ranges in A3:A6, TRUE, FALSE).

So, for me, the alternative I want to avoid is Example B where I would have to list every single number and then check the whole range. I would like to avoid this because these examples, as you can imagine, are on a much smaller scale then what I will actually be dealing with.

Hopefully my question was clear and thank you for any help in advance.

MMM

NBVC,

Thank you so much! That's exactly what I'm looking for.

However, I was wondering if you might be able to explain one part of the function for me. I understand all the functions but I can't figure out why the +0 needs to be there. I do see that if I exclude it, then it doesn't work. I would think after you use the LEFT function it should be a (integer<=E3) without the +0, but maybe I just don't know something about the LEFT function. Any clarity would be great. Thanks.

MMM

Ah, that makes a lot of sense. Thank you again so much, NBVC!

I have another element to this problem. So instead of it checking for just one number, I would like it to check to make sure multiple numbers are in there. Like making sure it includes not just 1, but also 1, 2, 3, 4, and 5. I tried replacing the part that refers to the cell E3 with AND(1, 2, 3, 4, 5). I was hoping the equation would check for all 5, but that's not working. Is there a different solution that you might be able to think of?

MMM

So, for this problem it doesn't involve Example B or Example C. Hm, I would like to have the same kind of if then statement. Looking at Example A, I would like to see if the numbers 301, 302, and 303 are in that range, not just one value. So if all of them are in there it would return True, and if not, then False. It's okay if in the equation you have to manually put in 301, 302, and 303 instead of a cell.


As a side question, is there a way to see if a number is in just cell C3 of Example B? I figure, it gets more complicated since there is a comma in there, cause ideally I would like to have all the ranges in one cell separated by commas, but it does seem like if possible it would be messy.

Thank you,
MMM

Thank you again NBVC. I appreciate all your help.

MMM

Either time has expired me from editing the first post or someone has removed it, but I don't have an edit button anymore for that first post. Are you able to change that?

MMM