Combining data in a new table

[mariong]

I have a table with products, prices and then quantity by customer - I would like to create a new table with the total price of the products by customer. I know I can create a million vlookups of the product quantity multiplied by a vlookup of the price but I know there must be a better way. So for example Column A is paper, staples, pen, paper clips Column B is prices $2.00, $0.50, $1,20, $0.25 then the following columns are customers - so customer X has 5 paper and 10 pens and the following customers are in the following columns. I want the new table to show customer X with $10, $0, $12.00 etc (so lookup customer quantity and multiply by price). I just need the suggestion on how to build this. Vlookups are too long - is there a command or function I should research that will help me make this? Thank you

[#2]

@mariong 

A noble offer from Mr.Sergei Baklan

can only recommend it :)))

https://techcommunity.microsoft.com/t5/excel/look-up-in-table-based-on-4-values/m-p/1839794#M79568

To continue tinkering yourself :)

XLOOKUP(
  D14,
  ($A$2:$A$9&$B$2:$B$9&$C$2:$C$9=A14&B14&C14)*$D$2:$D$9,
  $F$2:$F$9,
  "no such",
   -1

Hope it helped you

Nikolino

I know I don't know anything (Socrates)

[#3 · Accepted solution]

Although I think Power Query is more appropriate here. Add a MS Excel file (without sensitive data) to your project. Knowing the Excel version also be an advantage. So you could get a much more concrete solution than the one I sent. Thank you for your understanding and patience Nikolino I know I don't know anything (Socrates)

[#4]

@NikolinoDE I think you are 100% correct - so I have created a sample table as the information is confidential - so the first column is the product - then the price per product, the shipping per product and the extra per product - then all the customers by quantity - what I need is to create a new table with Customer 1 - bananas cost, bananas shipping, bananas extra cost, then oranges cost, oranges shipping, oranges extra (so I have the actual prices per category by customer rather than numbers) -thank you

[#5]

@mariong 

Hope I understood the translation correctly. Please look at the inserted file, is the result in the direction you want?

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)