IF and AND nested possible?
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.
Answers
-
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))
-
Thanks for your comment!
What exactly is this formula doing?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!