Forum Discussion

pivotalenergycouk's avatar
pivotalenergycouk
Copper Contributor
Jun 21, 2023

Excel Formula Query

Hi there, I'm trying to add a formula that looks up 2 different pieces of information dependant on whether the cell states Yes or No.

 

Any assistance would be welcomed!

    • pivotalenergycouk's avatar
      pivotalenergycouk
      Copper Contributor

      HansVogelaar

       

      Hello,

      I have a two tables of supplier costs which differ depending on if they fall in category 1 or category 2. 

      I want to be able to select from a dropdown list, category 1 for example, and the spreadsheet pull the costs over from the page containing category 1 supplier figures.  Vice versa if I select category 2.

      I have the table of costs and can get the spreadsheet to work if I lookup just one of the category figures but can't get it to work with both.  I have tried adding 'or' and 'and' but am really struggling.

       

      I hope this makes sense!

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        pivotalenergycouk 

        That's still not very specific, but you should be able to use a formula that looks like this:

         

        =VLOOKUP(item_cell, IF(category_cell="category 1", first_range, second_range), column_index, FALSE)

         

        where:

        item_cell is the cell containing the value you want to look up.

        category_cell is the cell with the drop down

        first_range is the range containing the supplier costs for category 1.

        second_range is the range containing the supplier costs for category 2.

        column_index is the column number of the column in first_range/second_range containing the price.

Resources