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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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.
 
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
 
Back
Top