Forum Discussion
Excel Formula Query
Please provide more specific, detailed information.
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!
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.