View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default How can I get Excel to automatically chose one of two answers?

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.