I'm trying to create a product page that will return a list of products based upon questions that a customer answers (or that the sales rep fills in). Basically I'm trying to return a value based on the answer to 2 questions (pulled from another sheet), which are in B and C, and the result will go into D (# needed).
<tbody>
</tbody>
There are 2 scenarios above that should hopefully illustrate what I'm trying to do! The customer can choose between 2 different models - classroom or professional. If they choose classroom, then the professional pipets need to have a quantity of 0. Additionally, if they need more than 7, then the set of 8 model should reflect that - they get 1 quantity of the bulk set, plus the remainder in singles.
In the 2nd scenario, the customer wants professional pipets, but only 5. So no classrooms are selected, and only 5 singles.
I tried doing a lookup table for this to make life easier, but I'm really having trouble wrapping my brain around how to do a VLOOKUP within an IF fxn, with an OR! Or maybe there is an easier solution that I don't know about (very very likely)!?
Thanks in advance!
item | question | # of stations | # needed |
classroom pipets, single | classroom | 10 | 2 |
professional pipets, single | classroom | 10 | 0 |
classroom pipets, set of 8 | classroom | 10 | 1 |
professional pipets, set of 8 | classroom | 10 | 0 |
classroom pipets, single | professional | 5 | 0 |
professional pipets, single | professional | 5 | 5 |
classroom pipets, set of 8 | professional | 5 | 0 |
professional pipets, set of 8 | professional | 5 | 0 |
<tbody>
</tbody>
There are 2 scenarios above that should hopefully illustrate what I'm trying to do! The customer can choose between 2 different models - classroom or professional. If they choose classroom, then the professional pipets need to have a quantity of 0. Additionally, if they need more than 7, then the set of 8 model should reflect that - they get 1 quantity of the bulk set, plus the remainder in singles.
In the 2nd scenario, the customer wants professional pipets, but only 5. So no classrooms are selected, and only 5 singles.
I tried doing a lookup table for this to make life easier, but I'm really having trouble wrapping my brain around how to do a VLOOKUP within an IF fxn, with an OR! Or maybe there is an easier solution that I don't know about (very very likely)!?
Thanks in advance!