How can I get Excel to automatically chose one of two answers?

[Guest]

I am trying to design an automobile purchase order form that uses option
codes and its corresponding prices. When I enter one option code on one
cell/row (i.e. LCB4 for Leather Upholstery), the cell next to it will
automatically kick out the correct price of $1450. However, another option
code (i.e. ZPP for an automobile Premium Package) at the price of $2900
includes Leather Upholstery. If ordered, I need to enter ZPP on another
cell/row that will generate the priceof $2900 next to ZPP; however, I do not
know how to get Excel to automatically recognize the ZPP code and change my
Leather Price of $1450 to $0 since it is included in the Premium Package.
Can anyone assist? Thanks again.

 

[L. Howard Kittle]

Hi Erwin,

Maybe something like this in F1 which returns a price from range J1:K5 for
LCB4.

=IF(E2="zpp",0,VLOOKUP(E1,J1:K5,2,0))

Where column E is where you are listing the options
E1 = LCB4
E2 = ZPP (or not)

HTH
Regards,
Howard

 

[Dave O]

I mocked up a scenario and used VLOOKUPs to price various option codes.
For the LCB4 code, leather seats, I used this formula:
=IF(SUMPRODUCT(--(B2:B6="ZPP"))>0,0,VLOOKUP(B6,I6:J10,2,0))

The formula looks for the existence of ZPP in the range where option
codes are entered. If it finds ZPP, it returns $0 for leather seats.
If ZPP is not found, it performs the VLOOKUP for LCB4 as usual. In
this mockup, B2:B6 is the range that holds option codes; I6:J10 is the
array holding the option code and the dollars associated with them.

 

[Guest]

Hi Howard,

Thank you so very much! Very very helpful.

Erwin

 

[Guest]

Hi Dave,

Thank you so much too! Very very helpful.

Not knowing all the functions of Excel, I was stumped for the longest time.

Best regards,
Erwin