IF and AND nested possible?

Stephen Maclean Rogers

Hello there!

I'm looking to have multiple nested IF statements which would allow me to have a cell say different things depending on what range the value falls within.

For example, I have the following data:

So far, I have made the following:

=IF(AND(E3>=15.5,E3<25.1),"Dorval Airport","")

But any further IF or AND statements appear as error.

Paul Newcome

It can be done, but you are setting yourself up for a rather long formula that has rather limited flexibility. Instead you can reference the table like so...

=INDEX([Column to pull from]:[Column to pull from], MATCH(MIN(COLLECT([Number Column]:[Number Column], [Number Column]:[Number Column], @cell >= E3)), [Number Column]:[Number Column], 0))

Stephen Maclean Rogers

Thanks for your comment!

What exactly is this formula doing?

Paul Newcome

The INDEX function is used to pull data from a table based on a row number and an optional column number. If you are only referencing a single column, then you can leave the column portion out.

The MATCH function (when looking at a single column) will provide the row number for the INDEX function.

To tell the MATCH function what to work on, we use a MIN/COLLECT to pull together all numbers that are greater than or equal to E3 and then pull the lowest number from that grouping.

And while typing this out, I realized I am approaching this backwards. The numbers you have listed are the low number for each and not the high number. that means we want to reverse the "MIN/COLLECT>=E3" to "MAX/COLLECT<=E3"

=INDEX([Column to pull from]:[Column to pull from], MATCH(MAX(COLLECT([Number Column]:[Number Column], [Number Column]:[Number Column], @cell <= E3)), [Number Column]:[Number Column], 0))

So we are using the MAX/COLLECT to pull all numbers that are less than or equal to E3 from the table and pulling the MAX from that.

So if E3 = 17.3, the largest number that is smaller than 17.3 is 15.5. The MATCH will use this to generate which row number 15.5 is on. That row number is then used to tell the INDEX function which row to pull from which will generate "Dorval Airport".

My apologies for the initial mixup.