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

Question

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?

Answer 1

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.