# Can MS Excel be used to reiteratively check intervals in multiple lines?

I want Excel to check intervals in all the lines in another sheet, finally returning true if my value is in any of those intervals, and fetching a value in that given line if true.

Let's call the value to be tested "X". I want Excel to check if X is between the values in cells A# and B#, returning the string in cell C# if true, or "" if ultimately false. That's simple enough to visualize as an IF function. My problem is that also I want the program to automatically check A#+1 and B#+1, returning C#+1, if the first check is false, A#+2 and B#+2, returning C#+2, if the second check is false, and so on. I'm dealing with sheets with more than a thousand lines, so it wouldn't be practical to just embed multiple IF statements in the formula.

That seems to be somehow possible with ARRAY or CSE functions, but I have no experience with them, and frankly couldn't figure out how to do it by myself, in any case.

Can you guys think of a way to do that using only Excel?

• Using Excel 365 you don't need to CSE enter anything anymore. Jul 20, 2020 at 20:29

The simplest way is to just use an IF statement and copy it down to the next row.

Or, with the new Excel Dynamic Array functions in Office 365, you can use a single formula that will automatically spill down as far as required.

The formula in cell E2 in the screenshot is

``````=IF((x>=A2:A6)*(x<=B2:B6),C2:C6,"")
``````

... and it automatically copied itself down. to return "bb" in the next row and blanks thereafter.

• Hmmm, I don't know if that will work in my case. I have to test a whole column of X, and it must give me the answer only of the line where the test is true. I'll try it out, in any case! Jul 20, 2020 at 23:05
• This DOES give you the answer on the line where the test is true. Do you understand what copying a formula down does? If that does not work for you, please edit your question, provide a data sample, the expected result, and explain the logic. Jul 21, 2020 at 4:07