Forum Discussion

Lary Spears's avatar
Lary Spears
Copper Contributor
Oct 05, 2017

Populating cells with information based on if one of two cells have contents

Hi,

I'm new to the drop down and VLOOKUP functions. Sorry if I don't explain this well.

 

I have created an order sheet with drop downs but I need two formulas 3 seperate cells based on which cell has information in it.

 

Example

Cell B8 is product, D8 is the Options for that product, and E, F, and G are 3 different types of pricing. Product has one set of pricing, Options have a different set of pricing. Only one of the two will be selected at a time.

 

How can I have the correct pricing show up based on if one field or the other is chosen? I have attached an example of the sheet.

 

Thanks in advance.

 

L

Excel Desktop
Formulas & Functions
  • Bryant Boyer's avatar
    Bryant Boyer
    Brass Contributor
    Oct 05, 2017

    Hey Lary,

     

    Can you clarify for me? Will there either be a product code entered in B8 OR an option code entered in D8 but not both?

     

    If that is the case, I would change your VLOOKUP function to check for a blank in the cell B8 first. I put this in E8:

     

    =IFERROR(IF($B$8="",VLOOKUP($D$8,Data!$E$2:$H$52,2,FALSE),VLOOKUP($B$8,Data!$A$2:$D$52,2,FALSE)),"")

    • Lary Spears's avatar
      Lary Spears
      Copper Contributor
      Oct 05, 2017

      Hi Bryant,

       

      Thanks for the response. Column 8 will not always have a model code. I need columns E, F, and G to know if I enter a value in column B there will be no option on that row, then if I enter a value in column D there will be no model.

       

      Column E, F, and G just need to be populated based on which (B or D) cell has data.

       

      Think of it as a car:

      B column=car model

      D column=options for that car (D9 wheels, D10 color, D11 trim...etc) Here's what it will look like.

      QtyModelBoardOptionsMSRPMAPDealer Cost
      1ASATDMP $2,857.00$1,999.99$1,380.00
         2TS$0.00$0.00$0.00
         3PB$72.00$50.00$35.00
         BIB$215.00$150.00$104.00

       

      My trouble is that on the spreadsheet, I may have to enter multiple models with options, but if I enter a model number on B10, the vlookup won't recognize it since it is formatted for the option column. Sometimes there will be two models, sometimes 6 or 7 with different option quantities. (hope that makes sense).

       

      Thanks again!!

       

      Lary

       

      • Bryant Boyer's avatar
        Bryant Boyer
        Brass Contributor
        Oct 06, 2017

        Hey Lary,

         

        Thanks for the clarification. Your example table makes it very clear.

         

        I actually think the formula I wrote to you last time will do the trick! Here is the table that I have using that formula:

         

        QtyModelBoardOptionsMSRPMAPDealer Cost
        1ASATDMP $2,857.00$1,999.99$1,380.00
           2TS$0.00$0.00$0.00
            3PB$72.00$50.00$35.00
           BIB$215.00$150.00$104.00

         

        In cell E8, the formula is

        =IFERROR(IF($B$8="",VLOOKUP($D$8,Data!$E$2:$H$52,2,FALSE),VLOOKUP($B$8,Data!$A$2:$D$52,2,FALSE)),"")

         

        In F8

        =IFERROR(IF($B$8="",VLOOKUP($D$8,Data!$E$2:$H$52,3,FALSE),VLOOKUP($B$8,Data!$A$2:$D$52,3,FALSE)),"")

         

        in G8

        =IFERROR(IF($B$8="",VLOOKUP($D$8,Data!$E$2:$H$52,4,FALSE),VLOOKUP($B$8,Data!$A$2:$D$52,4,FALSE)),"")

         

        And drag the three formulas down. They will do a VLOOKUP on the Model if there is one, or on the options if there is no model on that same row.

         

        I hope I've understood you! Reach out if I'm still confused :)

Resources