If/Then Help

Camille Adriao

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!

bcwilson.ca

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.

Paul Newcome

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

bcwilson.ca

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.

Camille Adriao

This worked! Thank you! I used the same formula to pull the remaining data from the cells instead of vlookup.