Nested IF / OR / VLOOKUP

[leightx]

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).

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!

 

[oldbrewer]

Re: Need help with nested IF / OR / VLOOKUP

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
	I choose classroom
	so all that is available to me is classroom pipettes single or classroom pipettes set of 8
	say I want 10 pipettes
	so I need a pack of 8 and 2 singles
				col E
	is this the situation ?
			select	classroom	needed	10
item	question	# of stations	# needed	CHOICE	quantity in pack
classroom pipets, single	classroom	10	2	yes	1
professional pipets, single	classroom	10	0
classroom pipets, set of 8	classroom	10	1	yes	8
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
					large packs		1
					singles		2
				columns E to H are automated

<colgroup><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Fluff]

Re: Need help with nested IF / OR / VLOOKUP

How about
Excel 2013 32 bit

	A	B	C	D
1	item	question	# of stations	# needed
2	classroom pipets, single	professional	5	0
3	professional pipets, single	professional	5	5
4	classroom pipets, set of 8	professional	5	0
5	professional pipets, set of 8	professional	5	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

New​

Worksheet Formulas Cell Formula D2 =IF(B2="classroom",C2-D4*8,0) D3 =IF(B3="professional",C3-D5*8,0) D4 =IF(B4="Classroom",IF(C4>8,FLOOR(C4/8,1),0),0) D5 =IF(B5="professional",IF(C5>8,FLOOR(C5/8,1),0),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[leightx]

Re: Need help with nested IF / OR / VLOOKUP

Thanks so much Fluff - that did the trick!!

 

[Fluff]

Re: Need help with nested IF / OR / VLOOKUP

Glad to help & thanks for the feedback