If/Then Help
Looking for help with a too complicated for me multiple/nesting If/Then Statement.
I would like to check the column labeled # of Current Booths  TSS and depending on the quantity return one of the range options from the example table in the column labeled Service Model Booth Range.
Based on what is returned in Service Model Booth Range, I would like to then populate the below fields with the corresponding quantity that ties into that range from the example table above. The corresponding columns for the 751+ Booths range would need to remain blank.
Thank you!
Best Answer

There are two ways to do this
I would do it as a combination.. but will explain the other way as well at the end
First I created an IF statement to calculate your "Service Model Booth Range"
=IF([# of Current Booths  TSS]@row < 51, "1  50 Booths", IF([# of Current Booths  TSS]@row < 101, "51 100 Booths", IF([# of Current Booths  TSS]@row < 201, "101  200 Booths", IF([# of Current Booths  TSS]@row < 301, "301  450 Booths", IF([# of Current Booths  TSS]@row < 451, "451  600 Booths", IF([# of Current Booths  TSS]@row < 601, "601  750 Booths", "751+ Booths"))))))
This will classify the number of booths in to the appropriate service model
Second, create a separate table exactly as you did in your screenshot
Finally, do a VLOOKUP from your one sheet to the other "matrix" and lookup up the booth range and the corresponding data to populate
=VLOOKUP([Service Model Booth Range]@row, {Service Model Booth Matrix}, 2, false)
=VLOOKUP([Service Model Booth Range]@row, {Service Model Booth Matrix}, 3, false)
and so on...
The other way would be to just do a VLOOKUP but then you would have to have a complete record for each quantity... 1,2,3....234,235.. and so on
You may be able to do an index match or something with so you don’t have the if and then the lookup.. but I would need more coffee to figure that one out.
Answers

Are you able to provide a screenshot with manually entered data that shows the end result you are wanting to accomplish?

There are two ways to do this
I would do it as a combination.. but will explain the other way as well at the end
First I created an IF statement to calculate your "Service Model Booth Range"
=IF([# of Current Booths  TSS]@row < 51, "1  50 Booths", IF([# of Current Booths  TSS]@row < 101, "51 100 Booths", IF([# of Current Booths  TSS]@row < 201, "101  200 Booths", IF([# of Current Booths  TSS]@row < 301, "301  450 Booths", IF([# of Current Booths  TSS]@row < 451, "451  600 Booths", IF([# of Current Booths  TSS]@row < 601, "601  750 Booths", "751+ Booths"))))))
This will classify the number of booths in to the appropriate service model
Second, create a separate table exactly as you did in your screenshot
Finally, do a VLOOKUP from your one sheet to the other "matrix" and lookup up the booth range and the corresponding data to populate
=VLOOKUP([Service Model Booth Range]@row, {Service Model Booth Matrix}, 2, false)
=VLOOKUP([Service Model Booth Range]@row, {Service Model Booth Matrix}, 3, false)
and so on...
The other way would be to just do a VLOOKUP but then you would have to have a complete record for each quantity... 1,2,3....234,235.. and so on
You may be able to do an index match or something with so you don’t have the if and then the lookup.. but I would need more coffee to figure that one out.

This worked! Thank you! I used the same formula to pull the remaining data from the cells instead of vlookup.
Help Article Resources
Categories
Check out the Formula Handbook template!