I have a spreadsheet which has

Column A - List of Products we sell
Column B - 2022 Prices
Column C - 2023 Prices
Column D - 2024 Prices

i want to create a spreadsheet where the user work out how much the total product costs.



In simple terms, if the User Selects 'Product A' and then '2022' - both drop down lists - then the result will be the price of the product in 2022, (answer from cell B2)

if the User Selects 'Product B ' and then '2023' - both drop down lists - then the result will be the price of the product B in 2023 , (answer will be in cell C3)

any ideas how I use VLoookup or similar in that way!

Please post a sample sheet ( no pics please) with some data and manually added expected results. Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi, see in attached file a possible solution...

Attached Files

ProductsPrices.xlsx (25.3 KB, 4 views)

Attached example sheet.

I have entered examples of the result I want in S11 and S12 given the inputs.

Attached Files

Benefit in Kind Rates.xlsx (26.2 KB, 3 views)

Hi, see the attached file (with an auxiliary column) ...

Attached Files

Benefit in Kind Rates.xlsx (20.5 KB, 4 views)

My advice is - redesign your data table. E.g. your price table will be like;
Column A - Product;
Column B - Year;
Column C - Price.

If you want to use Data Validation Lists to select product or year (either in your prices table, or in your report sheet), you also need according worksheets with products list and years list. Whenever a price for new product or year must be registered, this product or year must be registered in those tables at first. After that, the user can add new price to prices table. The additional bonus - users can't enter products or years with typos.

On your report sheet, you'll have data validation lists to select product and year, and the wanted price is calculated like as (I use Defined Table syntax in my example,you can replace Defined Table column references with standard range references wen you want. And in my example I defined cells with Data Validation Lists as Defined Names RepProduct and RepYear):
Also, when you define your prices table as Defined Table (e.g. PriceTable), or you apply manually the Autofilter to your table, the asked task isn't really needed at all - user can set autofilter for Product and Year columns, and gets it. Even more - with autofilter user can select e.g. several products, and a year, and gets a list of prices for those products!

Thank you - that works.

Need to sort out what happens if you want to add the NDE surcharge - but it's a help!

What's the NDE surcharge mean?

Certain vehicles has an additional surcharge - if you look at the data you will see that for some cars with a particular emission figure there is an additional % to be applied

I hope, I interpreted the data in the table correctly.

Attached Files

Benefit in Kind Rates NDE.xlsx (21.2 KB, 4 views)

Sorry - it's not the too added together - the surcharge in your example is 4% - so the answer should be 30.

Maybe it will be good now.

Attached Files

Benefit in Kind Rates NDE.xlsx (21.1 KB, 6 views)