Excel Formula Query

[pivotalenergycouk]

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!

[#2]

@pivotalenergycouk 

Please provide more specific, detailed information.

[#3]

@Hans Vogelaar

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!

[#4]

@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.