0

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?

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

1 Answer 1

0

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.

enter image description here

2
  • 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!
    – Larkas
    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.
    – teylyn
    Jul 21, 2020 at 4:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.